package com.kingdee.eas.fi.ar.app;

import com.kingdee.bos.BOSException;
import com.kingdee.bos.dao.IObjectPK;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.bos.dao.query.IQueryExecutor;
import com.kingdee.bos.dao.query.QueryExecutorFactory;
import com.kingdee.bos.db.TempTablePool;
import com.kingdee.bos.metadata.IMetaDataPK;
import com.kingdee.bos.metadata.MetaDataPK;
import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemCollection;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.bos.metadata.entity.SelectorItemCollection;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.bos.util.BOSUuid;
import com.kingdee.eas.basedata.assistant.CurrencyInfo;
import com.kingdee.eas.basedata.assistant.PeriodCollection;
import com.kingdee.eas.basedata.assistant.PeriodInfo;
import com.kingdee.eas.basedata.assistant.PeriodUtils;
import com.kingdee.eas.basedata.assistant.SystemStatusCtrolUtils;
import com.kingdee.eas.basedata.master.auxacct.AsstActTypeFactory;
import com.kingdee.eas.basedata.master.auxacct.AsstActTypeInfo;
import com.kingdee.eas.basedata.master.auxacct.AsstActTypeSysEnum;
import com.kingdee.eas.basedata.master.auxacct.IAsstActType;
import com.kingdee.eas.basedata.master.cssp.CustomerFactory;
import com.kingdee.eas.basedata.master.cssp.ICustomer;
import com.kingdee.eas.basedata.master.cssp.ISupplier;
import com.kingdee.eas.basedata.master.cssp.SupplierFactory;
import com.kingdee.eas.basedata.org.CompanyOrgUnitFactory;
import com.kingdee.eas.basedata.org.CompanyOrgUnitInfo;
import com.kingdee.eas.basedata.org.ICompanyOrgUnit;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.common.SysConstant;
import com.kingdee.eas.fi.ap.OtherBillType;
import com.kingdee.eas.fi.ap.VerificateBillTypeEnum;
import com.kingdee.eas.fi.ap.VerificationEnum;
import com.kingdee.eas.fi.ar.AccountAgeQueryParam;
import com.kingdee.eas.fi.ar.ArApCommonException;
import com.kingdee.eas.fi.ar.ArApQueryParam;
import com.kingdee.eas.fi.ar.ArApReportData;
import com.kingdee.eas.fi.ar.ArApReportTreeData;
import com.kingdee.eas.fi.ar.CAStmtQueryParam;
import com.kingdee.eas.fi.ar.IReportData;
import com.kingdee.eas.fi.ar.IReportQueryParam;
import com.kingdee.eas.fi.ar.ItemRptQueryParam;
import com.kingdee.eas.fi.ar.OtherBillTypeEnum;
import com.kingdee.eas.fi.ar.ReportInfo;
import com.kingdee.eas.fi.arap.app.util.TempTableUtil;
import com.kingdee.eas.framework.SystemEnum;
import com.kingdee.eas.scm.cal.util.DBUtil;
import com.kingdee.eas.util.ResourceBase;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.util.StringUtils;
import com.kingdee.util.db.SQLUtils;
import java.io.PrintStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;

public class ReportFacadeControllerBean extends
		AbstractReportFacadeControllerBean {
	private static Logger logger = Logger
			.getLogger("com.kingdee.eas.fi.ar.app.ReportFacadeControllerBean");

	protected IReportData _getARAgesReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		return getArApAgesReport(ctx, (AccountAgeQueryParam) filterParam, true);
	}

	private ArApQueryParam getArApMonthsSettlementReport(
			com.kingdee.bos.Context ctx, ArApQueryParam param)
			throws BOSException, EASBizException {
		long start = System.currentTimeMillis();

		SimpleDateFormat dtFormat = new SimpleDateFormat("yyyy-MM-dd");

		java.sql.Date dateFrom = new java.sql.Date(param.getLong("dateFrom"));
		dateFrom.setTime(dateFrom.getTime() + 86400000L);
		java.sql.Date dateTo = new java.sql.Date(param.getLong("dateTo"));
		dateTo.setTime(dateTo.getTime() + 86400000L);

		java.util.Locale locale = ctx.getLocale();

		Connection con = null;
		Statement stmt = null;

		String tempTable = param.getTempTable();

		String cussAcctSql = ArApReportHelper.getCussObjsSql(ctx, param);
		String ln = ctx.getLocale().getLanguage();
		String idsTblDefine = "( FId varchar(44), FName_" + ln
				+ " nvarchar(400), FNumber nvarchar(80) )";

		String tmpIdsTable = TempTableUtil.createTempTable(ctx, idsTblDefine);
		String tblDesc = "cussAcctIds";
		try {
			con = getConnection(ctx);

			String inSql = "insert into " + tmpIdsTable + " \r\n "
					+ cussAcctSql;
			logger.debug(tblDesc + " idsTmpTable " + tmpIdsTable + "\r\n"
					+ inSql);

			stmt = con.createStatement();
			stmt.execute(inSql);

			TempTablePool t = TempTablePool.getInstance(ctx);
			try {
				t.analyzeTable(con, tmpIdsTable);
			} catch (Exception e) {
				e.printStackTrace(System.out);
			}
		} catch (SQLException e) {
		} finally {
			SQLUtils.cleanup(stmt, con);
		}
		param.setCussAcctTempTable(tmpIdsTable);

		tempTable = TempTableUtil
				.createTempTable(
						ctx,
						" (FId varchar(44) NOT NULL PRIMARY KEY, FCompanyOrgUnitId varchar(44), FCussAcctNum nvarchar(200),FCussAcctName nvarchar(200),FCussAcctId varchar(44), FCurrencyNum nvarchar(200),FCurrencyName nvarchar(200),FCurrencyId varchar(44), FBillNumber nvarchar(200),FBillDate datetime, FBillTypeName nvarchar(2000), FNote nvarchar(2000), FDebitAmt decimal(28, 10) NOT NULL DEFAULT 0, FCreditAmt decimal(28, 10) NOT NULL DEFAULT 0, FSumCreditAmt decimal(28, 10) NOT NULL DEFAULT 0, FBalance decimal(28, 10) NOT NULL DEFAULT 0 , FMonth int NOT NULL DEFAULT 1 , FYear int NOT NULL DEFAULT 1900 , FOrderIndex int NOT NULL DEFAULT 0)");
		try {
			con = getConnection(ctx);

			param.setTempTable(tempTable);

			StringBuffer sql = new StringBuffer(200);
			sql.append(" insert into ").append(tempTable);
			sql.append(" select t1.FId FBillId");
			sql.append(" ,t1.FCompanyId");
			sql.append(" ,t1.FasstActNumber");
			sql.append(" ,t1.FasstActName_l2,t1.FAsstActID ");
			sql.append(" ,t1.FCurrencyId, t3.FNumber,t3.FName_l2");
			sql.append(" ,t1.FNumber FBillNum,t1.FBillDate FBillDate");
			sql.append(" ,case ");
			sql.append("  when t1.FBillType = ").append(102).append(" then ");
			sql.append("  '").append(OtherBillTypeEnum.InvoiceBill.toString())
					.append("'");
			sql.append("  when t1.FBillType = ").append(101).append(" then ");
			sql.append("  '").append(
					OtherBillTypeEnum.OtherReceivable.toString()).append("'");
			sql.append("  end ");
			sql.append(" ,case ");
			sql.append("  when t1.FBillType = ").append(102).append(" then ");

			sql.append("     t1.FAbstractName");
			sql.append("  when t1.FBillType = ").append(101).append(" then ");
			sql.append("     t1.FAbstractName");
			sql.append("  end ");
			sql.append(" ,t1.Famount,0,0,0");
			sql.append(" ,DAYOFMONTH(t1.FBillDate)");
			sql.append(" ,DAYOFYEAR(t1.FBillDate)");
			sql.append(" ,0");

			sql.append(" from t_ar_otherbill t1");

			sql.append(" inner join ").append(param.getCussAcctTempTable())
					.append(" t3 ");
			sql.append(" on t1.FAsstActID = t3.FId");
			sql
					.append(" left join t_bd_currency t4 on t1.Fcurrencyid = t4.fid ");

			sql.append(" where 1 = 1 ");
			if (!(StringUtils.isEmpty(param.getCurrencyId()))) {
				sql.append(" and t1.FCurrencyId = '").append(
						param.getCurrencyId()).append("' ");
			}
			sql.append("  and t1.FCompanyId = '").append(param.getCompanyId())
					.append("'");
			sql.append(" and t1.FBillStatus = 3 ");
			if (!(StringUtils.isEmpty(param.getString("SalesId")))) {
				sql.append(" and t1.FPersonId = '");
				sql.append(param.getString("SalesId")).append("' ");
			}

			sql.append(" and (");

			sql.append("  t1.FBillDate < {").append(dtFormat.format(dateTo))
					.append("}");
			sql.append(" and t1.FBillDate >= {").append(
					dtFormat.format(dateFrom)).append("}");

			sql.append(" or (");
			sql.append("  t1.FBillDate < {").append(dtFormat.format(dateFrom))
					.append("}");
			sql.append(" and t1.FUnVerifyAmount >0");
			sql.append(" )");
			sql.append(" )");
			sql.append(" and (t1.FBillType = ").append(102);
			sql.append(" or t1.FBillType = ").append(101);
			sql.append(" )");

			logger.debug("Aged bill Data \r\n" + sql);

			stmt = con.createStatement();
			stmt.execute(sql.toString());

			sql.setLength(0);
			sql.append(" insert into ").append(tempTable);
			sql.append(" select t1.FId FBillId");
			sql.append(" ,t1.FCompanyId");
			sql.append(" ,t1.FasstActNumber");
			sql.append(" ,t1.FasstActName_l2,t1.FAsstActID ");
			sql.append(" ,t1.FCurrencyId, t3.FNumber,t3.FName_l2");
			sql.append(" ,t1.FNumber FBillNum,t1.FBillDate FBillDate");
			sql.append(" ,case ");
			sql.append("  when t1.FBillType = ").append(102).append(" then ");
			sql.append("  '").append(OtherBillTypeEnum.InvoiceBill.toString())
					.append("'");
			sql.append("  when t1.FBillType = ").append(101).append(" then ");
			sql.append("  '").append(
					OtherBillTypeEnum.OtherReceivable.toString()).append("'");
			sql.append("  end ");
			sql.append(" ,case ");
			sql.append("  when t1.FBillType = ").append(102).append(" then ");

			sql.append("     t1.FAbstractName");
			sql.append("  when t1.FBillType = ").append(101).append(" then ");
			sql.append("     t1.FAbstractName");
			sql.append("  end ");
			sql.append(" ,t1.Famount,0,0,0");
			sql.append(" ,DAYOFMONTH(t1.FBillDate)");
			sql.append(" ,DAYOFYEAR(t1.FBillDate)");
			sql.append(" ,0");

			sql.append(" from t_ar_otherbill t1");

			sql.append(" inner join T_AR_VerificationBillentry ")
					.append(" t22");

			sql.append(" on t1.FId = t22.FBillId");
			sql.append(" inner join T_AR_VerificationBill t11");
			sql.append(" on t11.FId = t22.FParentId ");

			sql.append(" inner join ").append(param.getCussAcctTempTable())
					.append(" t3 ");
			sql.append(" on t1.FAsstActID = t3.FId");
			sql
					.append(" left join t_bd_currency t4 on t1.Fcurrencyid = t4.fid ");

			sql.append(" where 1 = 1 ");
			if (!(StringUtils.isEmpty(param.getCurrencyId()))) {
				sql.append(" and t1.FCurrencyId = '").append(
						param.getCurrencyId()).append("' ");
			}
			sql.append(" and t1.FCompanyId = '").append(param.getCompanyId())
					.append("'");
			sql.append(" and t1.FBillStatus = 3 ");
			if (!(StringUtils.isEmpty(param.getString("SalesId")))) {
				sql.append(" and t1.FPersonId = '");
				sql.append(param.getString("SalesId")).append("' ");
			}
			sql.append(" and (t1.FBillType = ").append(102);
			sql.append(" or t1.FBillType = ").append(101);
			sql.append(" )");

			sql.append(" and (");
			sql.append("  t1.FBillDate < {").append(dtFormat.format(dateFrom))
					.append("}");
			sql.append(" and t1.FUnVerifyAmount = 0");
			sql.append(" )");
			sql.append(" and t11.FBizDate > {").append(
					dtFormat.format(dateFrom)).append("}");

			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql.toString());

			String billA = ResourceBase.getString(
					"com.kingdee.eas.fi.arap.ARAPAutoGenerateResource",
					"97_ReportFacadeControllerBean", ctx.getLocale());
			String billB = ResourceBase.getString(
					"com.kingdee.eas.fi.arap.ARAPAutoGenerateResource",
					"98_ReportFacadeControllerBean", ctx.getLocale());
			String billC = ResourceBase.getString(
					"com.kingdee.eas.fi.arap.ARAPAutoGenerateResource",
					"99_ReportFacadeControllerBean", ctx.getLocale());

			sql.setLength(0);
			sql.append(" insert into ").append(tempTable);
			sql.append(" select t1.FId FBillId");
			sql.append(" ,t1.FCompanyId");
			sql.append(" ,t1.FPayerNumber");
			sql.append(" ,t1.FPayerName,t1.FPayerID ");
			sql.append(" ,t1.FCurrencyId, t3.FNumber,t3.FName_l2");
			sql.append(" ,t1.FNumber FBillNum,t1.FBizDate FBillDate");
			sql.append(" ,case ");
			sql.append("  when t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAGVwKgSfCqo2zU=").append("'")
					.append(" then ");
			sql.append("  '").append(billA).append("'");
			sql.append("  when t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAILwKgSfCqo2zU=").append("'")
					.append(" then ");
			sql.append("  '").append(billB).append("'");
			sql.append("  when t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAInwKgSfCqo2zU=").append("'")
					.append(" then ");
			sql.append("  '").append(billC).append("'");
			sql.append("  end ");
			sql.append(" ,t1.FDescription");
			sql.append(" ,t1.FAmount,0,0,0");
			sql.append(" ,DAYOFMONTH(t1.FBizDate)");
			sql.append(" ,DAYOFYEAR(t1.FBizDate)");
			sql.append(" ,0");

			sql.append(" from T_CAS_ReceivingBill t1");

			sql.append(" inner join ").append(param.getCussAcctTempTable())
					.append(" t3 ");
			sql.append(" on t1.FPayerID = t3.FId");
			sql
					.append(" left join t_bd_currency t4 on t1.Fcurrencyid = t4.fid ");

			sql.append(" where 1 = 1 ");
			if (!(StringUtils.isEmpty(param.getCurrencyId()))) {
				sql.append(" and t1.FCurrencyId = '").append(
						param.getCurrencyId()).append("' ");
			}
			sql.append(" \r\n and t1.FCompanyId = '").append(
					param.getCompanyId()).append("'");
			sql.append(" and t1.FBillStatus >= ").append(14);
			if (!(StringUtils.isEmpty(param.getString("SalesId")))) {
				sql.append(" and t1.FPersonId = '");
				sql.append(param.getString("SalesId")).append("' ");
			}

			sql.append(" and (t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAInwKgSfCqo2zU=").append("'");
			sql.append(" or t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAILwKgSfCqo2zU=").append("'");
			sql.append(" or t1.FRecBillTypeID = '").append(
					"DAWSqQEREADgAAGVwKgSfCqo2zU=").append("'");
			sql.append(" ) and (");
			sql.append("  t1.FBizDate < {").append(dtFormat.format(dateTo))
					.append("}");
			sql.append(" and t1.FVerifiedAmt = 0");
			sql.append(" or (t1.FVerifiedAmt > 0");
			sql.append(" and t1.FUnVerifiedAmt > 0");
			sql.append(" ))");

			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql.toString());

			sql.setLength(0);
			sql.append(" update ").append(tempTable).append(" as T");
			sql.append(" set (FCreditAmt,FSumCreditAmt)=(");
			sql.append(" select FTHISVERIFICATEAMT,0");
			sql.append(" from (");
			sql
					.append(" select t5.fid,sum(t2.FTHISVERIFICATEAMT) FTHISVERIFICATEAMT");
			sql.append(" from T_AR_VerificationBill t1");
			sql.append(" inner join T_AR_VerificationBillentry ").append(" t2");
			sql.append(" on t1.FId = t2.FParentId ");
			sql.append(" inner join ").append(tempTable).append(" t5");
			sql.append(" on t5.FId = t2.FBillId ");
			sql.append(" where (t5.FBillTypeName = '").append(
					OtherBillTypeEnum.InvoiceBill.toString()).append("'");
			sql.append(" or t5.FBillTypeName = '").append(
					OtherBillTypeEnum.OtherReceivable.toString()).append("'");
			sql.append(")");
			sql.append(" and t5.FBillDate < {").append(dtFormat.format(dateTo))
					.append("}");
			sql.append(" and t5.FBillDate >= {").append(
					dtFormat.format(dateFrom)).append("}");
			sql.append(" group by t5.fid");
			sql.append(" ) TT");
			sql.append(" Where TT.fid = T.fid");
			sql.append(" )");
			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql.toString());

			sql.setLength(0);
			sql.append(" update ").append(tempTable).append(" as T");
			sql.append(" set (FSumCreditAmt,FBalance)=(");
			sql
					.append(" select tt.FTHISVERIFICATEAMT,tt.FDebitAmt - tt.FTHISVERIFICATEAMT");
			sql
					.append(" from (select t5.fid,sum(t2.FTHISVERIFICATEAMT) FTHISVERIFICATEAMT,t5.FDebitAmt");
			sql.append(" from T_AR_VerificationBill t1");
			sql.append(" inner join T_AR_VerificationBillentry ").append(" t2");
			sql.append(" on t1.FId = t2.FParentId ");
			sql.append(" inner join ").append(tempTable).append(" t5");
			sql.append(" on t5.FId = t2.FBillId ");
			sql.append(" where (t5.FBillTypeName = '").append(
					OtherBillTypeEnum.InvoiceBill.toString()).append("'");
			sql.append(" or t5.FBillTypeName = '").append(
					OtherBillTypeEnum.OtherReceivable.toString()).append("'");
			sql.append(")");
			sql.append(" and t5.FBillDate < {").append(dtFormat.format(dateTo))
					.append("}");
			sql.append(" group by t5.fid,t5.FDebitAmt");
			sql.append(" ) TT");
			sql.append(" Where TT.fid = T.fid");
			sql.append(" )");
			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql.toString());

			sql.setLength(0);
			sql.append(" update ").append(tempTable).append(" as T");

			sql.append(" set (FSumCreditAmt,FBalance)=(");
			sql
					.append(" select tt.FTHISVERIFICATEAMT,tt.FDebitAmt - tt.FTHISVERIFICATEAMT");
			sql
					.append(" from (select t5.fid,sum(t2.FTHISVERIFICATEAMT) FTHISVERIFICATEAMT,t5.FDebitAmt");
			sql.append(" from T_AR_VerificationBill t1");
			sql.append(" inner join T_AR_VerificationBillentry ").append(" t2");
			sql.append(" on t1.FId = t2.FParentId ");
			sql.append(" inner join ").append(tempTable).append(" t5");
			sql.append(" on t5.FId = t2.FBillId ");
			sql.append(" where (t5.FBillTypeName = '").append(billA)
					.append("'");
			sql.append(" or t5.FBillTypeName = '").append(billB).append("'");
			sql.append(" or t5.FBillTypeName = '").append(billC).append("'");
			sql.append(")");
			sql.append(" and t5.FBillDate < {").append(dtFormat.format(dateTo))
					.append("}");
			sql.append(" group by t5.fid,t5.FDebitAmt");
			sql.append(" ) TT");
			sql.append(" Where TT.fid = T.fid");
			sql.append(" )");
			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql.toString());

			int month = dateFrom.getMonth();
			int year = dateFrom.getYear() - 1900;
			int k = dateDudge(dateTo, dateFrom);
			for (int i = 0; i < k; ++i) {
				month += i;
				month = 1;

				if (month > 12) {
					month -= 12;
					++year;
				}
				String monthStr = "" + month;
				if (month < 10)
					monthStr = "0" + monthStr;

				String createTime = year + "-" + monthStr + "-01 00:00:00.000";
				String id = BOSUuid.create("newbosid").toString();

				sql.setLength(0);
				sql.append(" insert into ").append(tempTable);

				sql.append(" select '").append(id).append("'");
				sql.append(" ,null");
				sql.append(" ,null");
				sql.append(" ,null,null ");
				sql.append(" ,null null,null");
				sql.append(" ,null");
				sql.append(" ,TO_DATE('").append(createTime).append("')");
				sql.append(ResourceBase.getString(
						"com.kingdee.eas.fi.arap.ARAPAutoGenerateResource",
						"100_ReportFacadeControllerBean", ctx.getLocale()));
				sql.append(" ,null");
				sql.append(" ,0,0,0,0,1");

				logger.debug("aged rpt sql \r\n" + sql);
				stmt.execute(sql.toString());

				sql.setLength(0);
				sql.append(" update ").append(tempTable).append(" as T");
				sql
						.append(" set (FDebitAmt,FCreditAmt,FSumCreditAmt,FBalancedecimal)=(");
				sql
						.append(" select FDebitAmt,FCreditAmt,FSumCreditAmt,FBalancedecimal");
				sql
						.append("(select sum(FDebitAmt) FDebitAmt,sum(FCreditAmt) FCreditAmt,sum(FSumCreditAmt) FSumCreditAmt,sum(FBalancedecimal) FBalancedecimal");
				sql.append(" from ").append(tempTable).append(" t1");
				sql.append(" where t1.FMonth = ").append(month);
				sql.append(" where t1.FYear = ").append(year);
				sql.append(ResourceBase.getString(
						"com.kingdee.eas.fi.arap.ARAPAutoGenerateResource",
						"101_ReportFacadeControllerBean", ctx.getLocale()));
				sql.append(" group by t1.FYear,t1.FMonth");
				sql.append(" ) TT");
				sql.append(" Where TT.fid = T.fid");
				sql.append(" and T.fid = '").append(id).append("'");
				sql.append(" )");
				logger.debug("aged rpt sql \r\n" + sql);
				stmt.execute(sql.toString());
			}
		} catch (SQLException ex) {
			throw new BOSException(ex);
		} finally {
			SQLUtils.cleanup(stmt, con);
		}

		String qryCussAcctId = param.getCussAcctId();

		List list = new ArrayList();

		StringBuffer sql = new StringBuffer(
				"select t1.FCussAcctNum ,t1.FCussAcctName ,t1.FCussAcctId");
		sql.append(" from ").append(param.getTempTable()).append(" t1");
		sql.append(" inner join ").append(param.getCussAcctTempTable()).append(
				" t2");
		sql.append(" on t1.FCussAcctId = t2.fid");
		sql
				.append(" group by t1.FCussAcctNum ,t1.FCussAcctName ,t1.FCussAcctId");
		sql.append(" order by t1.FCussAcctNum");

		con = getConnection(ctx);
		IRowSet rs = DBUtil.executeQuery(ctx, sql.toString());
		try {
			while (rs.next()) {
				HashMap hm = new HashMap();
				hm.put("FCussAcctNum", rs.getString("FCussAcctNum"));
				hm.put("FCussAcctName", rs.getString("FCussAcctName"));
				hm.put("FCussAcctId", rs.getString("FCussAcctId"));

				list.add(hm);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (list.size() > 0) {
			param.setCussAcctFromIds(list);

			if (StringUtils.isEmpty(qryCussAcctId))
				param.setCussAcctId((String) ((HashMap) list.get(0))
						.get("FCussAcctId"));
		}

		StringBuffer qSql = new StringBuffer();
		qSql.append("select * from ").append(param.getTempTable()).append(
				" t1 ");
		if (!(StringUtils.isEmpty(qryCussAcctId)))
			qSql.append(" where FCussAcctId = '").append(qryCussAcctId).append(
					"' ");
		qSql
				.append(" order by FCussAcctNum, FBillDate, FBillTypeName, FBillNumber");

		param.setString("SQL", qSql.toString());

		long end = System.currentTimeMillis();
		logger.debug(" tmpTable cost: " + (end - start));

		return param;
	}

	private int dateDudge(java.sql.Date a, java.sql.Date b) {
		Calendar aa = Calendar.getInstance();
		aa.setTimeInMillis(a.getTime());
		Calendar bb = Calendar.getInstance();
		bb.setTime(b);
		int month = Math.abs((1 - 1) * 12 + 2 - 2);

		return month;
	}

	private IReportData getArApAgesReport(com.kingdee.bos.Context ctx,
			AccountAgeQueryParam param, boolean fAR) throws BOSException,
			EASBizException {
		checkValid(param);
		param.setIsArRpt(fAR);

		long start = System.currentTimeMillis();

		String tempTable = param.getTempTable();
		if (StringUtils.isEmpty(tempTable)) {
			prepareAgesRpt(ctx, param, fAR);
		}

		IReportData rptData = getAgesData(ctx, param);

		long end = System.currentTimeMillis();
		logger.debug(" tmpTable cost: " + (end - start));

		return rptData;
	}

	private void setAsstAcctRangeId(com.kingdee.bos.Context ctx,
			ArApQueryParam param) throws BOSException, EASBizException {
		String cussAcctSql = ArApReportHelper.getCussObjsSql(ctx, param);
		param.setCussAcctTempTable(putIds2Table(ctx, cussAcctSql,
				"cussAcctIds", false));
	}

	private void prepareAgesRpt(com.kingdee.bos.Context ctx,
			AccountAgeQueryParam param, boolean fAR) throws BOSException,
			EASBizException {
		setAsstAcctRangeId(ctx, param);

		boolean fByBill = param.getAnalyseObjIndex() == 1;
		boolean fAgeByBillDate = param.getDateFldIndex() == 1;
		String tempTable = TempTableUtil.createTempTable(ctx,
				agedRptTempTable(param));

		String agedBillTable = TempTableUtil.createTempTable(ctx,
				agedBillsTableDefine(fByBill, fAgeByBillDate));

		Connection con = null;
		Statement stmt = null;
		try {
			con = getConnection(ctx);

			param.setTempTable(tempTable);

			String sql = ArApReportHelper.getAgedBillDataSql(ctx, param,
					agedBillTable, fAR);
			logger.debug("Aged bill Data \r\n" + sql);

			stmt = con.createStatement();
			stmt.execute(sql);

			sql = ArApReportHelper.getAgeRptSql(ctx, param, agedBillTable);
			logger.debug("aged rpt sql \r\n" + sql);
			stmt.execute(sql);

			sql = ArApReportHelper.getAgedBillSumSql(ctx, param);
			logger.debug("aged sum sql\r\n" + sql);
			stmt.execute(sql);
		} catch (SQLException ex) {
			throw new BOSException(ex);
		} finally {
			SQLUtils.cleanup(stmt, con);
		}
	}

	private IReportData getAgesData(com.kingdee.bos.Context ctx,
			AccountAgeQueryParam param) throws BOSException, EASBizException {
		if (StringUtils.isEmpty(param.getTempTable()))
			return null;

		IReportData rptData = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		boolean fArRpt = param.isArRpt();
		boolean fByBill = param.getAnalyseObjIndex() == 1;
		java.util.Locale locale = ctx.getLocale();

		StringBuffer qSql = new StringBuffer();
		qSql.append("select top ").append(param.getEndIndex());
		qSql.append(" * from ").append(param.getTempTable()).append(" t1 ");
		String qryCussAcctId = param.getCussAcctId();
		if (!(StringUtils.isEmpty(qryCussAcctId))) {
			qSql.append(" where FCussAcctId = '").append(qryCussAcctId).append(
					"' ");
		}

		if (fByBill) {
			qSql
					.append(" order by FCussNum, FCurrency, FDataType, FBillDate, FBillType, FBillNum");
		} else {
			qSql.append(" order by FCussNum, FCurrency, FDataType");
		}

		try {
			logger.debug("ageRpt qrySql\r\n" + qSql);
			con = getConnection(ctx);
			pstmt = con.prepareStatement(qSql.toString());
			rs = pstmt.executeQuery();

			int start = param.getStartIndex();
			int end = param.getEndIndex();
			int row = 0;
			List rptFlds = param.getReportFields();
			List dataLst = new ArrayList();
			ReportInfo rptInfo = null;
			do
				while (true) {
					if (!(rs.next()))
						break;
					row = rs.getRow();
					if (!(ArApReportHelper.isInRange(row, start, end)))
						break;
					rptInfo = new ReportInfo();
					String col = "";

					int i = 0;
					label397: for (int n = rptFlds.size(); i < n; ++i) {
						col = rptFlds.get(i).toString();

						if (col.equals("FBillType")) {
							int type = rs.getInt("FBillType");
							if (type < 0)
								break label397;

							String typeVal = getBillType(type,
									(fArRpt) ? 3 : 4, locale);

							rptInfo.put(col, typeVal);
						} else {
							rptInfo.put(col, rs.getObject(col));
						}
					}

					int rowType = rs.getInt("FDataType");
					rptInfo.setIsTotalLine(rowType != 0);

					dataLst.add(rptInfo);
				}
			while (row <= end);

			label453: rptData = new ArApReportData(dataLst);
			rptData.setTempTable(param.getTempTable());
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(rs, pstmt, con);
		}

		return rptData;
	}

	private void insertCAStmtData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		PeriodInfo countStartArPeriod = ArApReportHelper.getPeriodStartToCount(
				ctx, param, true);

		PeriodInfo countStartApPeriod = ArApReportHelper.getPeriodStartToCount(
				ctx, param, false);

		String sql = CaStmtItemSqlBuilder.getCAStmtSql(ctx, param,
				countStartArPeriod, countStartApPeriod);

		if (StringUtils.isEmpty(sql))
			return;

		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			logger.debug("castmt sql\r\n" + sql);
			con = getConnection(ctx);
			pstmt = con.prepareStatement(sql);
			pstmt.execute();
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(pstmt, con);
		}
	}

	private IReportData getCAStmtData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		if (StringUtils.isEmpty(param.getTempTable()))
			return null;

		IReportData rptData = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String startCol = "FStartBalanceAmt";
		String balanceCol = "FBalanceAmt";
		try {
			String sql = castmtFinalSql(param);
			logger.debug("caStmt qrySql\r\n" + sql);
			con = getConnection(ctx);

			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();

			int start = param.getStartIndex();
			int end = param.getEndIndex();
			List rptFlds = param.getReportFields();
			List dataLst = new ArrayList();
			String col = "";
			BigDecimal balance = SysConstant.BIGZERO;
			ReportInfo rptInfo = null;
			int row = -1;
			do
				while (true) {
					if (!(rs.next()))
						break;
					row = rs.getRow();

					if (!(ArApReportHelper.isInRange(row, start, end)))
						break;
					rptInfo = new ReportInfo();

					int i = 0;
					for (int n = rptFlds.size(); i < n; ++i) {
						col = rptFlds.get(i).toString();
						rptInfo.put(col, rs.getObject(col));
					}

					balance = (rs.getBigDecimal(startCol) != null) ? rs
							.getBigDecimal(startCol) : SysConstant.BIGZERO;

					if (rs.getBigDecimal("FDebitAmt") != null) {
						balance = balance.add(rs.getBigDecimal("FDebitAmt"));
					}
					if (rs.getBigDecimal("FCreditAmt") != null) {
						balance = balance.subtract(rs
								.getBigDecimal("FCreditAmt"));
					}

					rptInfo.put(balanceCol, balance);

					dataLst.add(rptInfo);
				}
			while (row <= end);

			label354: rptData = new ArApReportData(dataLst);
			rptData.setTempTable(param.getTempTable());
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(rs, pstmt, con);
		}

		return rptData;
	}

	private String castmtFinalSql(CAStmtQueryParam param) {
		List rptFlds = param.getReportFields();
		int fldSize = rptFlds.size();

		StringBuffer dataSql = new StringBuffer();
		dataSql.append("select top ").append(param.getEndIndex()).append(" ");
		for (int i = 0; i < fldSize; ++i) {
			dataSql.append(rptFlds.get(i));
			dataSql.append((i < fldSize - 1) ? ", " : "");
		}
		dataSql.append("\r\n from ").append(param.getTempTable());
		dataSql.append(" t1\r\n left join t_bd_currency t2 ");
		dataSql.append("on t2.FId = t1.FCurrencyId\r\n where FIsFinal = 1");
		dataSql.append(" order by t2.FNumber, FCussType, FCussNum");

		return dataSql.toString();
	}

	private String getBillType(int iType, int iMainType, java.util.Locale locale) {
		String billType = "";

		if ((iMainType == 4) && (OtherBillType.getEnum(iType) != null)) {
			billType = OtherBillType.getEnum(iType).getAlias(locale);
		} else if ((iMainType == 3)
				&& (OtherBillTypeEnum.getEnum(iType) != null)) {
			billType = OtherBillTypeEnum.getEnum(iType).getAlias(locale);
		} else if (iMainType == 0) {
			if (iType == 15) {
				billType = VerificateBillTypeEnum.BadAcct.getAlias(locale);
			} else {
				billType = (VerificationEnum.getEnum(iType) != null) ? VerificationEnum
						.getEnum(iType).getAlias(locale)
						: " ";
			}

		}

		return billType;
	}

	private void checkValid(ArApQueryParam param) throws BOSException,
			EASBizException {
		if (param == null) {
			throw new ArApCommonException(ArApCommonException.RPT_PARAM_NULL);
		}

		if (StringUtils.isEmpty(param.getCompanyId())) {
			throw new ArApCommonException(ArApCommonException.COMPANY_NULL);
		}

		if (param.getCussAcctTypeInfo() == null)
			throw new ArApCommonException(ArApCommonException.NOTSETASSTTYPE);
	}

	private String agedBillsTableDefine(boolean fByBill, boolean fAgeByBillDate) {
		StringBuffer tbl = new StringBuffer(200);
		tbl.append(" ( FCussType nvarchar(100), FCussAcctId varchar(44), ");
		tbl.append(" FCussNum nvarchar(100), FCussName nvarchar(400), ");
		tbl.append("FCurrency nvarchar(100), FBillDate dateTime, FRecPayDate");
		tbl
				.append(" dateTime, FUnVerifyAmt decimal(28, 10) NOT NULL DEFAULT 0");
		if (fByBill) {
			tbl.append(", FBillType int, FBillNum nvarchar(200),");
			tbl.append(" FBillId varchar(44), FDescription nvarchar(200)");
		}
		tbl.append(")");

		return tbl.toString();
	}

	private String agedRptTempTable(AccountAgeQueryParam param) {
		boolean fByBill = param.getAnalyseObjIndex() == 1;

		StringBuffer tbl = new StringBuffer(200);
		tbl.append(" (FCussAcctId varchar(44), ");

		List lstFlds = param.getReportFields();
		int amtFldIndex = param.getAmtColStartIndex();
		tbl.append(lstFlds.get(0)).append(" nvarchar(200), ");
		tbl.append(lstFlds.get(1)).append(" nvarchar(200), ");
		tbl.append(lstFlds.get(2)).append(" nvarchar(200)");
		if (fByBill) {
			tbl.append(",");

			tbl.append(lstFlds.get(3)).append(" dateTime, ");

			tbl.append(lstFlds.get(4)).append(" int default -1, ");

			tbl.append(lstFlds.get(5)).append(" nvarchar(200), ");

			tbl.append(lstFlds.get(6)).append(" nvarchar(200), ");

			tbl.append(lstFlds.get(7)).append(" varchar(44)");
		}
		int i = amtFldIndex;
		for (int fldSize = lstFlds.size(); i < fldSize; ++i) {
			tbl.append(", ").append(lstFlds.get(i));
			tbl.append(" decimal(28, 10) NOT NULL DEFAULT 0");
		}
		tbl.append(", FDataType int NOT NULL DEFAULT 0)");

		return tbl.toString();
	}

	protected IReportData _getAPAgesReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		return getArApAgesReport(ctx, (AccountAgeQueryParam) filterParam, false);
	}

	protected IReportData _getAPCAStmtItemReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkCAStmtItemParam(param);
		param.setIsArRpt(false);

		return getCAStmtItemRpt(ctx, param);
	}

	protected IReportData _getARCAStmtItemReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkCAStmtItemParam(param);
		param.setIsArRpt(true);

		return getCAStmtItemRpt(ctx, param);
	}

	protected ArApReportTreeData _getARCAStmtItemRptTreeData(
			com.kingdee.bos.Context ctx, IReportQueryParam filterParam)
			throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkCAStmtItemParam(param);
		param.setIsArRpt(true);

		return getCAStmtItemRptTreeData(ctx, param);
	}

	protected ArApReportTreeData _getAPCAStmtItemRptTreeData(
			com.kingdee.bos.Context ctx, IReportQueryParam filterParam)
			throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkCAStmtItemParam(param);
		param.setIsArRpt(false);

		return getCAStmtItemRptTreeData(ctx, param);
	}

	private ArApReportTreeData getCAStmtItemRptTreeData(
			com.kingdee.bos.Context ctx, CAStmtQueryParam param)
			throws BOSException, EASBizException {
		fetchCAStmtItemRpt(ctx, param);

		StringBuffer sql = new StringBuffer(200);
		sql.append("select distinct t1.fid id, t1.FName_");
		sql.append(ctx.getLocale().getLanguage());
		sql.append(" name, t1.fnumber number\r\n from ");
		sql.append(param.getCussAcctTempTable());
		sql.append(" t1 ");

		sql.append(" left outer join ").append(param.getTempTable()).append(
				" t2 ");
		sql.append(" on  t2.FCussAcctId = t1.FId ");
		sql.append(" where 1=1 ");
		if (param.isCustSupplAcctCuss())
			sql.append(" and t1.FIsByBizCode = 0");

		sql.append(" order by t1.fnumber");
		logger.debug("treeData sql: \r\n" + sql);
		IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());

		ArApReportTreeData treeData = new ArApReportTreeData();
		treeData.setTempTable(param.getTempTable());
		treeData.setAssistTempTable(param.getCussAcctTempTable());
		treeData.setRowSet(rs);
		return treeData;
	}

	private void fetchCAStmtItemRpt(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		String tempTable = param.getTempTable();
		if (!(StringUtils.isEmpty(tempTable)))
			return;

		SystemEnum sysEnum = (param.isArRpt()) ? SystemEnum.ACCOUNTSRECEIVABLE
				: SystemEnum.ACCOUNTSPAYABLE;

		PeriodInfo startPeriod = SystemStatusCtrolUtils.getStartPeriod(ctx,
				sysEnum, new ObjectUuidPK(param.getCompanyId()));

		if (startPeriod == null) {
			throw new ArApCommonException(
					ArApCommonException.AP_STARTPERIOD_NULL);
		}

		if (param.getBeginDate().before(startPeriod.getBeginDate())) {
			param.setBeginDate(new java.sql.Date(startPeriod.getBeginDate()
					.getTime()));
		}

		java.sql.Date endDate = param.getEndDate();
		endDate.setTime(endDate.getTime() + 86400000L);
		param.setEndDate(endDate);

		prepareCAStmtItemRpt(ctx, param);
	}

	private IReportData getCAStmtItemRpt(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		fetchCAStmtItemRpt(ctx, param);

		IReportData rptData = getCAStmtItemData(ctx, param);

		return rptData;
	}

	private void checkCAStmtItemParam(CAStmtQueryParam param)
			throws BOSException, EASBizException {
		checkValid(param);

		if ((param.getBeginDate() == null) || (param.getEndDate() == null)
				|| (param.getBeginDate().after(param.getEndDate()))) {
			throw new ArApCommonException(ArApCommonException.DATERANGE_INVALID);
		}
	}

	private void putCustSupIdByBizCode2Table(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		boolean fCusSupp = param.isCustSupplAcctCuss();

		String cussTable = param.getCussTable().toLowerCase();
		String nameFld = ArApReportHelper.getNameFld(ctx);
		String cussAcctIdsTable = null;

		String cussIdsTblDefine = null;
		String flds = null;
		String valFlds = null;

		cussIdsTblDefine = "( FId varchar(44), FBizAnalysisCode nvarchar(80) , "
				+ nameFld
				+ " nvarchar(400), FNumber nvarchar(80), FIsByBizCode int default 0)";

		flds = " t1.FId, t1." + nameFld + ", t1.FNumber ";
		valFlds = " (FId, " + nameFld + ", FNumber) ";

		String cussSql = null;

		cussSql = ArApReportHelper.getCussObjsSql(ctx, param);

		cussAcctIdsTable = TempTableUtil.createTempTable(ctx, cussIdsTblDefine);

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet sqlRs = null;
		try {
			con = getConnection(ctx);

			String insSql = "insert into " + cussAcctIdsTable + valFlds
					+ " \r\n" + cussSql;

			logger.debug("query cussAcctId, insSql \r\n" + insSql);
			pstmt = con.prepareStatement(insSql);
			pstmt.execute();

			param.setCussAcctTempTable(cussAcctIdsTable);

			if (!(fCusSupp)) {
				return;
			}
			StringBuffer hasBizCodeSql = new StringBuffer();
			hasBizCodeSql
					.append("select distinct FBizAnalysisCodeId\r\n from ");
			hasBizCodeSql.append(cussTable);
			hasBizCodeSql.append(" t1 inner join ").append(cussAcctIdsTable);
			hasBizCodeSql.append(" t2 on t1.fid = t2.fid\r\n where ");
			hasBizCodeSql.append(" t1.FBizAnalysisCodeId is not null ");

			logger.debug("query cussAcctId, queryBizCodeSql \r\n"
					+ hasBizCodeSql);
			pstmt = con.prepareStatement(hasBizCodeSql.toString());
			sqlRs = pstmt.executeQuery();

			List bizCodeSet = new ArrayList();
			StringBuffer bizCodeIds = new StringBuffer();
			while (sqlRs.next()) {
				bizCodeIds.append("'").append(sqlRs.getString(1));
				bizCodeIds.append("',");
				bizCodeSet.add(sqlRs.getString(1));
			}
			if (bizCodeIds.length() > 0) {
				bizCodeIds.deleteCharAt(bizCodeIds.length() - 1);
			} else {
				return;
			}
			IAsstActType iAsstType = AsstActTypeFactory.getLocalInstance(ctx);
			AsstActTypeSysEnum cussEnum = (param.isCustomerAcctCuss()) ? AsstActTypeSysEnum.PROVIDER
					: AsstActTypeSysEnum.CUSTOMER;

			AsstActTypeInfo otherTypeInfo = iAsstType.getAsstActType(cussEnum);
			param.setOtherTypeId(otherTypeInfo.getString("id"));

			String sql = getInsertSql_SameBizCode(ctx, param, bizCodeIds
					.toString(), flds);

			logger.debug("cussAcctSql:\r\n" + sql);
			pstmt = con.prepareStatement(sql);
			pstmt.execute();

			TempTablePool t = TempTablePool.getInstance(ctx);
			try {
				t.createIndex(cussAcctIdsTable, "FId", false, false);
			} catch (Exception e) {
				e.printStackTrace(System.out);
			}
			try {
				t.analyzeTable(con, cussAcctIdsTable);
			} catch (Exception e) {
				e.printStackTrace(System.out);
			}

			updateBizCodeNumber(con, cussAcctIdsTable, bizCodeSet);
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(sqlRs, pstmt, con);
		}
	}

	private String getInsertSql_SameBizCode(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param, String bizCodeIds, String flds)
			throws BOSException, EASBizException {
		FilterInfo cuFilter;
		String cussTable = param.getCussTable().toLowerCase();
		boolean fCustomer = param.isCustomerAcctCuss();
		IObjectPK cuPK = new ObjectUuidPK(param.getCuId());
		String cuSql = null;
		if (fCustomer) {
			ISupplier iSupp = SupplierFactory.getLocalInstance(ctx);
			cuFilter = iSupp
					.getDatabaseDFilter(cuPK, "t1.FId", "t1.FAdminCUId");

			cuSql = cuFilter.toString();
		} else {
			ICustomer iCust = CustomerFactory.getLocalInstance(ctx);
			cuFilter = iCust
					.getDatabaseDFilter(cuPK, "t1.FId", "t1.FAdminCUId");

			cuSql = cuFilter.toString();
		}

		String valFlds = " (FId, FName_" + ctx.getLocale().getLanguage()
				+ ", FNumber, FIsByBizCode) ";

		StringBuffer sql = new StringBuffer();

		sql.append("insert into ").append(param.getCussAcctTempTable());
		sql.append(valFlds);
		sql.append("\r\n select distinct ").append(flds);
		sql.append(", 1 FIsByBizCode\r\n from ").append(cussTable);
		sql.append(" t1 where t1.FBizAnalysisCodeId in (");
		sql.append(bizCodeIds).append(") and");
		sql.append(" t1.FId not in (select Fid from ");
		sql.append(param.getCussAcctTempTable()).append(" )");

		sql.append("\r\n union all \r\n");
		String otherTbl = (fCustomer) ? "t_bd_supplier" : "t_bd_customer";

		sql.append(" select ").append(flds);
		sql.append(", 1 FIsByBizCode\r\n from ").append(otherTbl);
		sql.append(" t1 where t1.FBizAnalysisCodeId in (");
		sql.append(bizCodeIds).append(") and ").append(cuSql);

		return sql.toString();
	}

	private void updateBizCodeNumber(Connection con, String cussAcctIdsTable,
			List bizCodeSet) throws SQLException {
		StringBuffer sql = new StringBuffer();
		sql.append(" update ").append(cussAcctIdsTable);
		sql.append("\r\n set FBizAnalysisCode = (select FNumber");
		sql.append(" from t_bd_bizAnaylysisCode where FId");
		sql.append(" = ?)\r\n where FId in (select FId from");
		sql.append(" t_bd_customer where FBizAnalysisCodeId = ?");
		sql.append(" union all\r\n   select Fid from t_bd_supplier");
		sql.append(" where FBizAnalysisCodeId = ?)");

		PreparedStatement pstmt = null;
		try {
			logger.debug("cussAcctSql updateBizCodeSql: \r\n" + sql);
			pstmt = con.prepareStatement(sql.toString());
			int i = 0;
			for (int n = bizCodeSet.size(); i < n; ++i) {
				String bizCodeId = (String) bizCodeSet.get(i);
				pstmt.setString(1, bizCodeId);
				pstmt.setString(2, bizCodeId);
				pstmt.setString(3, bizCodeId);

				pstmt.execute();
			}

		} catch (SQLException e) {
		} finally {
			SQLUtils.cleanup(pstmt);
		}
	}

	private IReportData getCAStmtItemData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		String tempTable = param.getTempTable();
		boolean fCustSupp = param.isCustSupplAcctCuss();
		String cussAcctTempTable = param.getCussAcctTempTable();
		if ((StringUtils.isEmpty(tempTable))
				|| ((fCustSupp) && (StringUtils.isEmpty(cussAcctTempTable)))) {
			logger.error("the temp tbl or the cussAcct temp tbl is null!!!");
			return null;
		}

		IReportData rptData = null;

		StringBuffer sql = new StringBuffer();
		sql.append("select top ").append(param.getEndIndex());
		sql.append(" * \r\n from ").append(tempTable);
		sql.append(" t1 where t1.FCussAcctId = ? ");
		if (fCustSupp) {
			sql.append("\r\n or t1.FBizAnalysisCode in (select ");
			sql.append(" FBizAnalysisCode from ");
			sql.append(cussAcctTempTable);
			sql.append("\r\n  where FId = ? )");
		}
		sql.append("\r\n order by FCurrency, FDatatype, FBillDate, ");
		sql.append("FBillNum, FBillType");

		logger.debug("get itemRptData \r\n" + sql + "\r\n cussAcctId: "
				+ param.getCussAcctId());

		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String balanceCol = "FBalanceAmt";
		try {
			cn = getConnection(ctx);

			pstmt = cn.prepareStatement(sql.toString());
			pstmt.setString(1, param.getCussAcctId());
			if (fCustSupp) {
				pstmt.setString(2, param.getCussAcctId());
			}
			rs = pstmt.executeQuery();

			int start = param.getStartIndex();
			int end = param.getEndIndex();
			int iType = 0;
			int iMainType = 0;

			String billType = "";
			String col = "";
			List rptFlds = param.getReportFields();
			List dataLst = new ArrayList();
			ReportInfo rptInfo = null;
			BigDecimal balance = SysConstant.BIGZERO;
			int dataType = -1;
			int row = -1;
			do
				while (true) {
					if (!(rs.next()))
						break;
					row = rs.getRow();

					dataType = rs.getInt("FDataType");
					if (dataType == 0) {
						balance = rs.getBigDecimal("FBalanceAmt");
					} else if (dataType == 1) {
						BigDecimal amt = rs.getBigDecimal("FDebitAmt");
						if (amt != null) {
							balance = balance.add(amt);
						}
						amt = rs.getBigDecimal("FCreditAmt");
						if (amt != null) {
							balance = balance.subtract(amt);
						}
					}

					if (!(ArApReportHelper.isInRange(row, start, end)))
						break;
					rptInfo = new ReportInfo();
					int i = 0;
					for (int n = rptFlds.size(); i < n; ++i) {
						col = rptFlds.get(i).toString();
						if (col.equalsIgnoreCase("FBillType")) {
							if (dataType != 1)
								break;

							iType = rs.getInt(col);
							iMainType = rs.getInt("FMainBillType");
							if ((iMainType == 1) || (iMainType == 2)) {
								billType = rs.getString("FRecPayType");
							} else {
								billType = getBillType(iType, iMainType, ctx
										.getLocale());
							}

							label600: rptInfo.put(col, billType);
						} else {
							rptInfo.put(col, rs.getObject(col));
						}
					}
					if (dataType == 2) {
						rptInfo.setIsTotalLine(true);
						rptInfo.put(balanceCol, null);
					} else {
						rptInfo.put(balanceCol, balance);
					}
					dataLst.add(rptInfo);
				}
			while (row <= end);

			label663: rptData = new ArApReportData(dataLst);
			rptData.setTempTable(param.getTempTable());

			java.util.Map dataMap = new HashMap();
			dataMap.put("cussAcctTbl", cussAcctTempTable);
			rptData.setAssistData(dataMap);
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(rs, pstmt, cn);
		}

		return rptData;
	}

	private void addCAItemTotalLine(com.kingdee.bos.Context ctx,
			String tempTable, boolean fCustSupp) throws BOSException,
			EASBizException {
		String res = "com.kingdee.eas.fi.ar.ArReport";
		String endLbl = ResourceBase.getString(res, "Lbl_CAItemTotal", ctx
				.getLocale());

		StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(tempTable);
		sql.append("\r\n (FDescription, FCussAcctId,");
		sql.append(" FCurrencyId, FCurrency, FDebitAmt, FCreditAmt,");
		sql.append(" FBalanceAmt, FIsFinal, FDataType)\r\n select N'");
		sql.append(endLbl).append("' FDescription, FCussAcctId");
		sql.append(", FCurrencyId,FCurrency, sum(FDebitAmt) FDebitAmt, ");
		sql.append(" sum(FCreditAmt) FCreditAmt, sum(FBalanceAmt) +");
		sql.append(" sum(FDebitAmt)-sum(FCreditAmt) FBalanceAmt,");
		sql.append(" 1 FIsFinal, 2 FDataType\r\n from ");
		sql.append(tempTable).append(" where FIsUseBizCode = 0");
		sql.append(" and FDataType <= 1\r\n");
		sql.append(" group by  FCussAcctId, FCurrencyId, FCurrency");

		Connection con = null;
		Statement stmt = null;
		try {
			con = getConnection(ctx);
			logger.debug("caitem sum: \r\n" + sql);
			stmt = con.createStatement();
			stmt.execute(sql.toString());

			if (fCustSupp) {
				sql.delete(0, sql.length());
				sql.append("insert into ").append(tempTable);
				sql.append(" (FDescription, FBizAnalysisCode, ");
				sql.append(" FCurrencyId, FCurrency, FDebitAmt, FCreditAmt,");
				sql.append(" FBalanceAmt, FIsFinal, FDataType)\r\n select N'");
				sql.append(endLbl).append("' FDescription,  ");
				sql.append(" FBizAnalysisCode, FCurrencyId, FCurrency, ");
				sql.append(" sum(FDebitAmt) FDebitAmt, ");
				sql.append(" sum(FCreditAmt) FCreditAmt, sum(FBalanceAmt) +");
				sql.append(" sum(FDebitAmt)-sum(FCreditAmt) FBalanceAmt,");
				sql.append(" 1 FIsFinal, 2 FDataType\r\n from ");
				sql.append(tempTable);
				sql.append("\r\n where FIsUseBizCode = 1 and FDataType <= 1");
				sql.append("\r\n group by FBizAnalysisCode,");
				sql.append(" FCurrencyId, FCurrency");

				logger.debug("caItem BizCode CustSupp sum\r\n" + sql);
				stmt.execute(sql.toString());
			}
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(stmt, con);
		}
	}

	private void prepareCAStmtItemRpt(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		putCustSupIdByBizCode2Table(ctx, param);

		String fromNum = param.getAdminOrgFromNum();
		String toNum = param.getAdminOrgToNum();
		if ((!(StringUtils.isEmpty(fromNum)))
				|| (!(StringUtils.isEmpty(toNum)))) {
			String adminOrgIds = getQueryRangeAdminOrgs(ctx, true, fromNum,
					toNum, param.getCuId());

			if (StringUtils.isEmpty(adminOrgIds))
				return;

			param.setAdminOrgIds(adminOrgIds);
		}

		Connection con = null;
		Statement stmt = null;
		String tmpTable = TempTableUtil.createTempTable(ctx,
				caItemTempTableDefine());

		TempTablePool t = TempTablePool.getInstance(ctx);
		try {
			t.createIndex(tmpTable, "FCurrency, FCurrencyID, FCussAcctId",
					false, false);
		} catch (Exception e) {
			e.printStackTrace(System.out);
		}
		try {
			t.analyzeTable(con, tmpTable);
		} catch (Exception e) {
			e.printStackTrace(System.out);
		}
		try {
			con = getConnection(ctx);

			param.setTempTable(tmpTable);

			insertCAItemStartData(ctx, param);

			stmt = con.createStatement();
			stmt.execute(CaStmtItemSqlBuilder
					.getCAItemDataSql(ctx, param, true));

			stmt.execute(CaStmtItemSqlBuilder.getCAItemDataSql(ctx, param,
					false));

			if (param.isCustSupplAcctCuss()) {
				String sql = "update "
						+ tmpTable
						+ " set FIsUseBizCode = 1 where FBizAnalysisCode is not null";

				logger.debug("set CaItemRpt FIsUseBizCode\r\n" + sql);
				stmt.execute(sql);

				sql = "delete "
						+ tmpTable
						+ " where FIsFinal = 0 and FBizAnalysisCode is not null";

				logger.debug("del  sql\r\n" + sql);
				stmt.execute(sql);
			}
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(stmt, con);
		}

		addCAItemTotalLine(ctx, param.getTempTable(), param
				.isCustSupplAcctCuss());

		removeCastmtBizBalZeroData(ctx, param);
	}

	private void insertCAItemStartData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException,
			SQLException {
		String nameFld = ArApReportHelper.getNameFld(ctx);
		PeriodInfo startArCountPeriod = ArApReportHelper.getPeriodStartToCount(
				ctx, param, true);

		PeriodInfo startApCountPeriod = ArApReportHelper.getPeriodStartToCount(
				ctx, param, false);

		Connection con = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(ctx);
			stmt = con.createStatement();

			String initSql = caItemStartInsertSql(ctx, param);
			logger.debug("caItem insertStart sql: \r\n" + initSql);
			stmt.execute(initSql);

			StringBuffer sql = new StringBuffer();
			sql.append("select tSum.FCussAcctId, t2.FId FCurrencyId, t2.");
			sql.append(nameFld).append(" FCurrency, ");
			sql.append("sum(tSum.FAmt) FBalanceAmt\r\n from (\r\n");
			sql.append(CaStmtItemSqlBuilder.getCAItemStartSql(param,
					startArCountPeriod, startApCountPeriod));

			sql.append("\r\n) tSum left join t_bd_Currency t2 on ");
			sql.append("tSum.FCurrencyId = t2.FId \r\n");
			sql.append(" group by tSum.FCussAcctId, ");
			sql.append("t2.FId, t2.").append(nameFld);

			logger.debug("caItem start balance sql:\r\n" + sql);
			rs = stmt.executeQuery(sql.toString());

			StringBuffer uSql = new StringBuffer();
			uSql.append("update ").append(param.getTempTable());
			uSql.append(" set FBalanceAmt = ? where FCurrency = ? ");
			uSql.append(" and FCurrencyId = ? and FCussAcctId = ?");
			pstmt = con.prepareStatement(uSql.toString());

			boolean fBatch = false;
			while (rs.next()) {
				pstmt.setBigDecimal(1, rs.getBigDecimal(4));
				pstmt.setString(2, rs.getString(3));
				pstmt.setString(3, rs.getString(2));
				pstmt.setString(4, rs.getString(1));
				pstmt.addBatch();
				fBatch = true;
			}

			if (fBatch)
				pstmt.executeBatch();

			if (param.isCustSupplAcctCuss()) {
				sql.delete(0, sql.length());
				sql.append(" insert into ").append(param.getTempTable());
				sql.append("\r\n (FBizAnalysisCode, FCurrencyId,");
				sql.append("FCurrency, FBillDate, FDescription, ");
				sql.append("FBalanceAmt, FIsFinal, FDataType)\r\n select ");
				sql.append("FBizAnalysisCode,FCurrencyId, ");
				sql.append(" FCurrency, FBillDate, FDescription, ");
				sql.append(" sum(FBalanceAmt) FBalanceAmt,");
				sql.append(" 1 FIsFinal, 0 FDataType ");
				sql.append("\r\n from ").append(param.getTempTable());
				sql.append("\r\n where FBizAnalysisCode is not null ");
				sql.append(" and FDataType = 0\r\n group by");
				sql.append(" FBizAnalysisCode, FCurrencyId, FCurrency,");
				sql.append(" FBillDate, FDescription");
				logger.debug("sum the init data with bizCode\r\n" + sql);
				stmt.execute(sql.toString());
			}
		} catch (SQLException e) {
		} finally {
			SQLUtils.cleanup(pstmt);
			SQLUtils.cleanup(rs, stmt, con);
		}
	}

	private String caItemStartInsertSql(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) {
		String res = "com.kingdee.eas.fi.ar.ArReport";
		String startLbl = ResourceBase.getString(res, "Lbl_CAItemStart", ctx
				.getLocale());

		String nameFld = ArApReportHelper.getNameFld(ctx);
		boolean fCusSupp = param.isCustSupplAcctCuss();
		SimpleDateFormat dtFormat = new SimpleDateFormat("yyyy-MM-dd");

		StringBuffer sql = new StringBuffer(100);
		sql.append("insert into ").append(param.getTempTable());
		sql.append("\r\n (").append((fCusSupp) ? "FBizAnalysisCode," : "");
		sql.append("FCussAcctId, FCurrencyId, FCurrency, FBillDate, ");
		sql.append("FDescription, FIsFinal, FDataType)\r\n select ");
		sql.append((fCusSupp) ? "t3.FBizAnalysisCode," : "");
		sql.append(" t3.FId as FCussAcctId, t2.FId FCurrencyId, t2.");
		sql.append(nameFld).append(" FCurrency, {");
		sql.append(dtFormat.format(param.getBeginDate()));
		sql.append("} FBillDate, N'").append(startLbl);
		sql.append("' FDescription, 0 FIsFinal, 0 FDataType ");
		sql.append("\r\n from t_bd_currency as");
		sql.append(" t2\r\n cross join ").append(param.getCussAcctTempTable());
		sql.append(" as t3\r\n where 1= 1 ");
		if (param.isAllCurrency()) {
			sql.append(" and (t2.FId != '");
			sql.append(CurrencyInfo.GENERAL_LOCAL_CURRENCY_ID);
			sql.append("' and t2.FId != '");
			sql.append(CurrencyInfo.GENERAL_REPORT_CURRENCY_ID);
			sql.append("') ");
		} else {
			sql.append(" and t2.FId = '");
			sql.append(param.getCurrencyId()).append("' ");
		}

		return sql.toString();
	}

	private void removeCastmtBizBalZeroData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException {
		if ((!(param.isBizZero())) && (!(param.isBalZero())))
			return;

		Connection cn = null;
		PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;

		String tempTable = param.getTempTable();
		boolean fItemRpt = param.isItemRpt();
		boolean fCustSupp = param.isCustSupplAcctCuss();
		StringBuffer delSql = new StringBuffer();
		if (fItemRpt) {
			delSql.append("delete from ").append(tempTable);
			delSql.append(" where FCurrencyId = ? and (");
			delSql.append(" FCussAcctId = ? ");
			if (fCustSupp) {
				delSql.append(" or FBizAnalysisCode = ?");
			}
			delSql.append(" )");
		} else {
			delSql.append("delete from ").append(tempTable);
			delSql.append(" where FCurrency = ? and FCussNum = ? ");
			delSql.append(" and FIsFinal = 1");
		}
		String sql = null;
		try {
			cn = getConnection(ctx);
			stmt = cn.createStatement();

			boolean fBatch = false;
			if (param.isBizZero()) {
				logger.debug("del the bizAmt zero: \r\n" + delSql);
				pstmt = cn.prepareStatement(delSql.toString());
				if (fItemRpt) {
					sql = "select distinct FCurrencyId, FCussAcctId "
							+ ((fCustSupp) ? ", FBizAnalysisCode from "
									: " from ")
							+ tempTable
							+ "\r\n where FDebitAmt = 0  and FCreditAmt = 0 and FDataType = 2";
				} else {
					sql = "select distinct FCurrency, FCussNum from "
							+ tempTable
							+ "\r\n where FDebitAmt = 0  and FCreditAmt = 0 and FIsFinal = 1";
				}

				logger.debug("select the bizAmt zero: \r\n" + sql);
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					pstmt.setString(1, rs.getString(1));
					pstmt.setString(2, rs.getString(2));
					if ((fItemRpt) && (fCustSupp))
						pstmt.setString(3, rs.getString(3));

					pstmt.addBatch();
					fBatch = true;
				}
				if (fBatch)
					pstmt.executeBatch();
			}
			if (param.isBalZero()) {
				fBatch = false;
				logger.debug("del the bal zero sql: \r\n" + delSql);
				pstmt = cn.prepareStatement(delSql.toString());
				if (fItemRpt) {
					sql = "select distinct FCurrencyId, FCussAcctId"
							+ ((fCustSupp) ? ", FBizAnalysisCode from "
									: " from ") + tempTable
							+ "\r\n where FDataType = 2 and FBalanceAmt = 0";
				} else {
					sql = "select distinct FCurrency, FCussNum from "
							+ tempTable
							+ " where (FStartBalanceAmt + FDebitAmt -FCreditAmt) = 0 and FIsFinal =1";
				}

				logger.debug("select the bal zero: \r\n" + sql);
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					pstmt.setString(1, rs.getString(1));
					pstmt.setString(2, rs.getString(2));
					if ((fItemRpt) && (fCustSupp))
						pstmt.setString(3, rs.getString(3));

					pstmt.addBatch();
					fBatch = true;
				}
				if (fBatch)
					pstmt.executeBatch();
			}

		} catch (SQLException ex) {
		} finally {
			SQLUtils.cleanup(pstmt);
			SQLUtils.cleanup(rs, stmt, cn);
		}
	}

	protected IReportData _getARCAStmtReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkValid(param);
		param.setIsArRpt(true);
		return getCAStmtRpt(ctx, filterParam);
	}

	private IReportData getCAStmtRpt(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		if ((param.getBeginDate() == null) || (param.getEndDate() == null)
				|| (param.getBeginDate().after(param.getEndDate()))) {
			throw new ArApCommonException(ArApCommonException.DATERANGE_INVALID);
		}
		SystemEnum sysEnum = (param.isArRpt()) ? SystemEnum.ACCOUNTSRECEIVABLE
				: SystemEnum.ACCOUNTSPAYABLE;

		PeriodInfo startPeriod = SystemStatusCtrolUtils.getStartPeriod(ctx,
				sysEnum, new ObjectUuidPK(param.getCompanyId()));

		if (startPeriod == null) {
			throw new ArApCommonException(
					ArApCommonException.AP_STARTPERIOD_NULL);
		}

		if (param.getBeginDate().before(startPeriod.getBeginDate())) {
			throw new ArApCommonException(
					ArApCommonException.DATE_NOT_IN_PERIOD);
		}

		String tempTable = param.getTempTable();
		if (StringUtils.isEmpty(tempTable)) {
			java.sql.Date endDate = param.getEndDate();
			endDate.setTime(endDate.getTime() + 86400000L);
			param.setEndDate(endDate);

			prepareCAStmtRpt(ctx, param);
		}

		IReportData rptData = getCAStmtData(ctx, param);

		return rptData;
	}

	private void prepareCAStmtRpt(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException, EASBizException {
		putCustSupIdByBizCode2Table(ctx, param);

		boolean fCusSupp = param.isCustSupplAcctCuss();

		String tmpTable = TempTableUtil.createTempTable(ctx,
				caStmtTempTableDefine(fCusSupp));

		param.setTempTable(tmpTable);

		String fromNum = param.getAdminOrgFromNum();
		String toNum = param.getAdminOrgToNum();
		if ((!(StringUtils.isEmpty(fromNum)))
				|| (!(StringUtils.isEmpty(toNum)))) {
			String adminOrgIds = getQueryRangeAdminOrgs(ctx, true, fromNum,
					toNum, param.getCuId());

			if (StringUtils.isEmpty(adminOrgIds))
				return;

			param.setAdminOrgIds(adminOrgIds);
		}

		addCaStmtStartData(ctx, param);

		insertCAStmtData(ctx, param);

		if (fCusSupp) {
			AsstActTypeInfo qryCussType = param.getCussAcctTypeInfo();
			mergeDataWithSameBizCode(ctx, tmpTable, qryCussType, false);
		}

		sumCAStmtRptData(ctx, tmpTable, fCusSupp);

		removeCastmtBizBalZeroData(ctx, param);
	}

	private void mergeDataWithSameBizCode(com.kingdee.bos.Context ctx,
			String tmpTable, AsstActTypeInfo qryCussType, boolean fItemRpt)
			throws BOSException, EASBizException {
		BOSUuid qryCussTypeId = qryCussType.getId();

		StringBuffer sql = new StringBuffer();
		Connection con = null;
		Statement qryStmt = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = getConnection(ctx);

			sql.append(" select distinct FCussNum, FCussName,");
			sql.append(" FCussAcctId, FBizAnalysisCode from ");
			sql.append(tmpTable).append("\r\n where FCussTypeId = '");
			sql.append(qryCussTypeId).append(
					"' and FBizAnalysisCode is not null");
			sql.append("\r\n order by FBizAnalysisCode asc, FCussNum desc ");
			qryStmt = con.createStatement();

			logger
					.debug("mergeData sameBizCode, selectAsstAcct, bizCode Sql: \r\n"
							+ sql);
			rs = qryStmt.executeQuery(sql.toString());

			sql.delete(0, sql.length());
			sql.append("update ").append(tmpTable);
			sql.append(" set FCussNum = ?, FCussName = ?, ");
			sql.append("FCussAcctId = ? where FCussTypeId != ? and ");
			sql.append(" FBizAnalysisCode = ?");

			logger.debug("mergeData sameBizCode sql: \r\n" + sql);
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(4, qryCussTypeId.toString());
			while (rs.next()) {
				pstmt.setString(1, rs.getString(1));
				pstmt.setString(2, rs.getString(2));
				pstmt.setString(3, rs.getString(3));
				pstmt.setString(5, rs.getString(4));

				logger.debug("mergeData sameBizCode sqlParam: \r\n"
						+ rs.getString(1) + ", " + rs.getString(2) + ", "
						+ rs.getString(3) + ", " + rs.getString(4));

				pstmt.executeUpdate();
			}

			sql.delete(0, sql.length());
			sql.append("update ").append(tmpTable).append(" set");
			sql.append(" FCussType = ?, FCussTypeId = ?");
			sql.append(" where FCussTypeId != ?");

			logger
					.debug("mergeData sameBizCode, setCussType Id Sql&Param: \r\n"
							+ sql
							+ "\r\n"
							+ qryCussType.getName()
							+ ", "
							+ qryCussTypeId);

			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, qryCussType.getName());
			pstmt.setString(2, qryCussTypeId.toString());
			pstmt.setString(3, qryCussTypeId.toString());
			pstmt.executeUpdate();
		} catch (SQLException ex) {
		} finally {
			SQLUtils.cleanup(pstmt);
			SQLUtils.cleanup(rs, qryStmt, con);
		}
	}

	private void sumCAStmtRptData(com.kingdee.bos.Context ctx, String tmpTable,
			boolean fCusSupp) throws BOSException {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into ").append(tmpTable);
		sql.append("(").append((fCusSupp) ? "FBizAnalysisCode," : "");
		sql.append(" FCussTypeId, FCussType, FCussNum, FCussName,");
		sql.append(" FCussAcctId, FCurrency, FCurrencyId,");
		sql.append(" FStartBalanceAmt, FDebitAmt, FCreditAmt,");
		sql.append(" FBalanceAmt, FIsFinal) \r\nselect ");
		sql.append((fCusSupp) ? "FBizAnalysisCode," : "");
		sql.append("FCussTypeId, FCussType, FCussNum, FCussName,");
		sql.append(" FCussAcctId, FCurrency, FCurrencyId,\r\n");
		sql.append(" sum(FStartBalanceAmt) FStartBalanceAmt,");
		sql.append(" sum(FDebitAmt) FDebitAmt, ");
		sql.append(" sum(FCreditAmt) FCreditAmt, ");
		sql.append(" sum(FBalanceAmt) FBalanceAmt, 1 FIsFinal\r\n from ");
		sql.append(tmpTable).append("\r\n group by ");
		sql.append((fCusSupp) ? "FBizAnalysisCode," : "");
		sql.append(" FCussTypeId, FCussType,");
		sql.append(" FCussNum,FCussName, FCussAcctId,");
		sql.append(" FCurrency, FCurrencyId ");

		logger.debug("final sql: \r\n" + sql);
		DbUtil.execute(ctx, sql.toString());

		if (!(fCusSupp)) {
			return;
		}

		sql.delete(0, sql.length());
		sql.append(" update ").append(tmpTable).append(" as t1");
		sql.append("\r\n set (FStartBalanceAmt, FDebitAmt, ");
		sql.append("FCreditAmt, FBalanceAmt) = (  \r\n");
		sql.append("   select distinct t3.FStartBalanceAmt, t3.FDebitAmt, ");
		sql.append("t3.FCreditAmt, t3.FBalanceAmt\r\n");
		sql.append("   from ").append(tmpTable).append(" as t2,\r\n");
		sql.append("    (select FBizAnalysisCode, FCurrencyId, ");
		sql.append("sum(FStartBalanceAmt) FStartBalanceAmt, ");
		sql.append("sum(FDebitAmt) FDebitAmt, ");
		sql.append("sum(FCreditAmt) FCreditAmt, sum(FBalanceAmt) FBalanceAmt");
		sql.append("\r\n    from ").append(tmpTable).append(" as t2\r\n");
		sql.append("     where t2.FIsFinal = 1 and t2.FBizAnalysisCode is ");
		sql.append("not null\r\n    group by FBizAnalysisCode, FCurrencyId");
		sql.append("    ) as t3 where t2.FBizAnalysisCode = ");
		sql.append(" t3.FBizAnalysisCode and t2.FCurrencyId = t3.FCurrencyId");
		sql.append(" and t2.FIsFinal = 1 ");
		sql.append(" and t1.FBizAnalysisCode = t2.FBizAnalysisCode ");
		sql.append(" and t1.FCurrencyId = t2.FCurrencyId ");
		sql.append(")\r\n where t1.FIsFinal = 1 ");

		logger.debug("merge bizCode Amt sql: \r\n" + sql);
		DbUtil.execute(ctx, sql.toString());
	}

	private void addCaStmtStartData(com.kingdee.bos.Context ctx,
			CAStmtQueryParam param) throws BOSException {
		String nameFld = ArApReportHelper.getNameFld(ctx);
		String tmpAcctTbl = param.getCussAcctTempTable();
		boolean fCusSupp = param.isCustSupplAcctCuss();

		StringBuffer sql = new StringBuffer();
		sql.append("insert into ").append(param.getTempTable());
		sql.append("(").append((fCusSupp) ? "FBizAnalysisCode," : "");
		sql.append("FCussTypeId, FCussType, FCussNum, FCussName,");
		sql.append(" FCussAcctId, FCurrency, FCurrencyId) \r\n select ");
		sql.append((fCusSupp) ? "t1.FBizAnalysisCode," : "");
		sql.append(" t2.FId FCussTypeId, t2.").append(nameFld);
		sql.append(" FCussType, t1.FNumber FCussNum,\r\n t1.");
		sql.append(nameFld).append(" FCussName, t1.FId FCussAcctId, t3.");
		sql.append(nameFld).append(" FCurrency, t3.FId FCurrencyId\r\n from ");
		sql.append(tmpAcctTbl).append(" t1, t_bd_AsstActType t2,");
		sql.append(" t_bd_currency t3\r\n where t2.FId = '");
		sql.append(param.getCussTypeId()).append("' ");
		sql.append("and t1.FIsByBizCode = 0 ");

		if (param.isAllCurrency()) {
			sql.append(" and (t3.FId != '");
			sql.append(CurrencyInfo.GENERAL_LOCAL_CURRENCY_ID);
			sql.append("' and t3.FId != '");
			sql.append(CurrencyInfo.GENERAL_REPORT_CURRENCY_ID);
			sql.append("') ");
		} else {
			sql.append(" and  t3.FId = '").append(param.getCurrencyId());
			sql.append("' ");
		}

		logger.debug("castmt init BizCurrency sql: \r\n" + sql);
		DbUtil.execute(ctx, sql.toString());
	}

	private String caStmtTempTableDefine(boolean fCusSupp) {
		StringBuffer sql = new StringBuffer();
		sql.append(" (");
		if (fCusSupp)
			sql.append("FBizAnalysisCode nvarchar(80), ");

		sql.append(" FCussTypeId varchar(44), ");
		sql.append(" FCussType nvarchar(200), ");
		sql.append(" FCussNum nvarchar(100), ");
		sql.append(" FCussName nvarchar(400), ");
		sql.append(" FCussAcctId varchar(44), ");
		sql.append(" FCurrency nvarchar(200), ");
		sql.append(" FCurrencyId varchar(44), ");
		sql.append(" FStartBalanceAmt decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FDebitAmt  decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FCreditAmt  decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FBalanceAmt decimal(28, 10) NOT NULL DEFAULT 0,");
		sql.append(" FIsFinal int NOT NULL DEFAULT 0)");

		return sql.toString();
	}

	private String caItemTempTableDefine() {
		StringBuffer sql = new StringBuffer();
		sql.append(" (FBillId varchar(44), ");

		sql.append("FBizAnalysisCode nvarchar(80), ");
		sql.append(" FIsUseBizCode int default 0, ");
		sql.append(" FCussAcctId varchar(44), ");
		sql.append(" FCurrencyId varchar(44), ");
		sql.append(" FCurrency nvarchar(100), ");
		sql.append(" FBillDate dateTime, ");
		sql.append(" FBillType int DEFAULT -1, ");
		sql.append(" FRecPayType nvarchar(100), ");
		sql.append(" FBillNum nvarchar(100), ");
		sql.append(" FVoucherType nvarchar(100), ");
		sql.append(" FVoucherNum nvarchar(100), ");
		sql.append(" FDescription nvarchar(200), ");
		sql.append(" FDebitAmt decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FCreditAmt decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FBalanceAmt decimal(28, 10) NOT NULL DEFAULT 0, ");

		sql.append(" FMainBillType int NOT NULL DEFAULT -1, ");
		sql.append(" FIsFinal int default 1, ");
		sql.append(" FDataType int)");

		return sql.toString();
	}

	protected IReportData _getAPCAStmtReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		CAStmtQueryParam param = (CAStmtQueryParam) filterParam;
		checkValid(param);
		param.setIsArRpt(false);

		return getCAStmtRpt(ctx, filterParam);
	}

	private String getQueryRangeAdminOrgs(com.kingdee.bos.Context ctx,
			boolean fAllCuAdmin, String fromNum, String toNum, String cuId)
			throws BOSException, EASBizException {
		IMetaDataPK cussQryPK = new MetaDataPK(
				"com.kingdee.eas.basedata.org.app.AdminOrgUnitQuery");

		IQueryExecutor exec = QueryExecutorFactory.getLocalInstance(ctx,
				cussQryPK);

		exec
				.setObjectView(getAdminRangeView(fAllCuAdmin, fromNum, toNum,
						cuId));

		boolean fBlank = ((StringUtils.isEmpty(fromNum)) && (StringUtils
				.isEmpty(toNum)))
				|| (StringUtils.isEmpty(fromNum));

		String tbl = putIds2Table(ctx, exec.getSQL(), "adminOrgRange", fBlank);
		return " select fid from " + tbl;
	}

	private EntityViewInfo getAdminRangeView(boolean fAllCuAdmin,
			String fromNum, String toNum, String cuId) {
		EntityViewInfo view = new EntityViewInfo();
		FilterInfo filter = new FilterInfo();
		view.setFilter(filter);
		view.getSelector().add("id");
		view.getSelector().add("name");
		view.getSelector().add("number");

		if (!(StringUtils.isEmpty(fromNum))) {
			filter.getFilterItems().add(
					new FilterItemInfo("number", fromNum,
							CompareType.GREATER_EQUALS));
		}

		if (!(StringUtils.isEmpty(toNum))) {
			filter.getFilterItems()
					.add(
							new FilterItemInfo("number", toNum,
									CompareType.LESS_EQUALS));
		}

		if (!(fAllCuAdmin)) {
			filter.getFilterItems().add(new FilterItemInfo("CU.id", cuId));
		}

		return view;
	}

	protected IReportData _getARItemReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		ItemRptQueryParam param = (ItemRptQueryParam) filterParam;
		checkItemRptParam(param);
		param.setIsArRpt(true);
		return getArApItemRpt(ctx, param);
	}

	protected IReportData _getAPItemReport(com.kingdee.bos.Context ctx,
			IReportQueryParam filterParam) throws BOSException, EASBizException {
		ItemRptQueryParam param = (ItemRptQueryParam) filterParam;
		checkItemRptParam(param);
		param.setIsArRpt(false);
		return getArApItemRpt(ctx, param);
	}

	protected ArApReportTreeData _getARItemRptTreeData(
			com.kingdee.bos.Context ctx, IReportQueryParam filterParam)
			throws BOSException, EASBizException {
		ItemRptQueryParam param = (ItemRptQueryParam) filterParam;
		checkItemRptParam(param);
		param.setIsArRpt(true);
		return getArApItemRptTreeData(ctx, param);
	}

	protected ArApReportTreeData _getAPItemRptTreeData(
			com.kingdee.bos.Context ctx, IReportQueryParam filterParam)
			throws BOSException, EASBizException {
		ItemRptQueryParam param = (ItemRptQueryParam) filterParam;
		checkItemRptParam(param);
		param.setIsArRpt(false);
		return getArApItemRptTreeData(ctx, param);
	}

	private ArApReportTreeData getArApItemRptTreeData(
			com.kingdee.bos.Context ctx, ItemRptQueryParam param)
			throws BOSException, EASBizException {
		if (StringUtils.isEmpty(param.getTempTable())) {
			prepareArApItemRpt(ctx, param);
		}

		String treeDataTbl = null;
		StringBuffer sql = new StringBuffer(200);
		if (param.isSumByCussAcct()) {
			treeDataTbl = param.getCussAcctTempTable();
		} else if (param.isSumByDept()) {
			treeDataTbl = param.getAdminOrgTempTable();
		} else if (param.isSumByPerson()) {
			treeDataTbl = param.getPersonTempTable();
		}

		String nameFld = ArApReportHelper.getNameFld(ctx);
		sql.append("select distinct t1.fid id,");
		if (param.isSumByCussAcct()) {
			sql.append(" t1.").append(nameFld).append(" name,");
		} else {
			sql.append(" case when t1.");
			sql.append(nameFld).append(" is null then N'");
			sql.append(ResourceBase
					.getString("com.kingdee.eas.fi.ar.ArReport.Lbl_blank", ctx
							.getLocale()));

			sql.append("' else t1.");
			sql.append(nameFld).append(" end name,");
		}
		sql.append(" t1.fnumber number\r\n from ");
		sql.append(treeDataTbl);

		sql.append(" t1\r\n ");

		sql.append(" LEFT OUTER JOIN ");
		sql.append(param.getTempTable());
		sql.append(" T2 ON t2.FCussAcctId = t1.FId ");

		sql.append(" order by t1.fnumber");

		logger.debug("treeData sql: \r\n" + sql);
		IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());

		ArApReportTreeData treeData = new ArApReportTreeData();
		treeData.setTempTable(param.getTempTable());
		treeData.setAssistTempTable(param.getCussAcctTempTable());
		treeData.setRowSet(rs);
		return treeData;
	}

	private IReportData getArApItemRpt(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		String tempTable = param.getTempTable();
		long sTime = new Date().getTime();
		if (StringUtils.isEmpty(tempTable)) {
			prepareArApItemRpt(ctx, param);
			System.out.println("========================"
					+ (new Date().getTime() - sTime)
					+ "============================");
		}
		IReportData rptData = getArApItemData(ctx, param);
		return rptData;
	}

	private void checkItemRptParam(ItemRptQueryParam param)
			throws BOSException, EASBizException {
		checkValid(param);
		if ((param.getBeginDate() == null) || (param.getEndDate() == null)
				|| (param.getBeginDate().after(param.getEndDate()))) {
			logger.error("the date range has not specified or invalid.");
			throw new ArApCommonException(ArApCommonException.DATERANGE_INVALID);
		}
	}

	private void prepareArApItemRpt(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		setAsstAcctRangeId(ctx, param);

		if (StringUtils.isEmpty(param.getCussAcctTempTable())) {
			logger.warn("not cussAccts");
			return;
		}

		setAdminOrgIds2Tbl(ctx, param);

		if (StringUtils.isEmpty(param.getAdminOrgTempTable())) {
			logger.warn("not adminOrgs");
			return;
		}

		setPersonIds2Tbl(ctx, param);

		if (StringUtils.isEmpty(param.getPersonTempTable())) {
			logger.warn("not persons");
			return;
		}

		String tempTable = TempTableUtil.createTempTable(ctx,
				itemRptTempTableDefine());

		param.setTempTable(tempTable);

		TempTablePool t = TempTablePool.getInstance(ctx);
		try {
			t.createIndex(tempTable, "Fid", true, false);
			t.createIndex(tempTable, "FPeriodId, FCurrency, FCussAcctId",
					false, false);
		} catch (Exception e) {
			e.printStackTrace(System.out);
		}

		insertItemRptData(ctx, param);
	}

	private void synchMoveBillRedundancyFiled(com.kingdee.bos.Context ctx)
			throws BOSException {
		Connection con = null;
		try {
			con = getConnection(ctx);
			ArrayList sqlList = new ArrayList();
			StringBuffer sql = new StringBuffer();

			sql
					.append(" update T_CAS_ReceivingBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FPayerid,B.FPayerTypeID,B.FBizDate");
			sql.append(" FROM T_CAS_ReceivingBill A ");
			sql
					.append(" INNER JOIN T_CAS_ReceivingBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId) ");
			sql
					.append(" Where A.FISTRANSBILL = 1 and A.FIsTransOtherBill = 0 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(")");
			sqlList.add(sql.toString());

			sql.delete(0, sql.length());

			sql
					.append(" update T_CAS_PaymentBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FPayeeid ,B.FPayeeTypeID,B.FBizDate");
			sql.append(" FROM T_CAS_PaymentBill A ");
			sql
					.append(" INNER JOIN T_CAS_PaymentBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId) ");
			sql
					.append(" Where A.FISTRANSBILL = 1 and A.FIsTransOtherBill = 0 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(" )");
			sqlList.add(sql.toString());

			sql.delete(0, sql.length());

			sql
					.append(" update T_CAS_PaymentBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FAsstActID ,B.FAsstActTypeID,B.FBillDate");
			sql.append(" FROM T_CAS_PaymentBill A ");
			sql
					.append(" INNER JOIN T_AR_OtherBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId) ");
			sql
					.append(" Where A.FIsTransOtherBill = 1 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(" )");
			sqlList.add(sql.toString());

			sql.delete(0, sql.length());

			sql
					.append(" update T_CAS_ReceivingBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FAsstActID ,B.FAsstActTypeID,B.FBillDate");
			sql.append(" FROM T_CAS_ReceivingBill A ");
			sql
					.append(" INNER JOIN T_AP_OtherBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId)");
			sql
					.append(" Where A.FIsTransOtherBill = 1 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(" )");
			sqlList.add(sql.toString());

			sql.delete(0, sql.length());

			sql
					.append(" update T_AP_OtherBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FAsstActID ,B.FAsstActTypeID,B.FBillDate");
			sql.append(" FROM T_AP_OtherBill A ");
			sql
					.append(" INNER JOIN T_AP_OtherBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId) ");
			sql.append(" Where  A.FIsReverseBill = 0 ");
			sql
					.append(" AND A.FIsTransBill = 1 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(" )");
			sqlList.add(sql.toString());

			sql.delete(0, sql.length());

			sql
					.append(" update T_AR_OtherBill as T set (FAdminOrgUnitId_SourceBill,FPersonID_SourceBill,");
			sql
					.append(" FAsstActID_SourceBill,FAsstActTypeID_SourceBill,FBillDate_SourceBill)=");
			sql.append(" (");
			sql
					.append(" select B.FAdminOrgUnitId,B.FPersonID,B.FAsstActID ,B.FAsstActTypeID,B.FBillDate");
			sql.append(" FROM T_AR_OtherBill A ");
			sql
					.append(" INNER JOIN T_AR_OtherBill B ON (A.FSourceBillID = B.FID");
			sql.append(" AND A.FCompanyId = B.FCompanyId) ");
			sql.append(" Where  A.FIsReverseBill = 0 ");
			sql
					.append(" AND A.FIsTransBill = 1 and A.FBillDate_SourceBill is null");
			sql.append(" AND A.Fid= T.Fid");
			sql.append(" )");
			sqlList.add(sql.toString());

			DBUtil.execBatchSql(con, sqlList, true);
		} catch (SQLException e) {
		} finally {
			SQLUtils.cleanup(con);
		}
	}

	private void setAdminOrgIds2Tbl(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		String fromNum = param.getAdminOrgFromNum();
		String toNum = param.getAdminOrgToNum();

		IMetaDataPK cussQryPK = new MetaDataPK(
				"com.kingdee.eas.fi.arap.app.AdminOrgUnitQuery");

		IQueryExecutor exec = QueryExecutorFactory.getLocalInstance(ctx,
				cussQryPK);

		boolean fAllCuAdmin = (param.isLinkSum())
				|| (param.isAllCuAdminPerson());
		exec.setObjectView(getAdminRangeView(fAllCuAdmin, fromNum, toNum, param
				.getCuId()));

		String sql = exec.getSQL();
		param.setAdminOrgTempTable(putIds2Table(ctx, sql, "adminOrgRange",
				param.isIncludeNullAdmin()));
	}

	private void setPersonIds2Tbl(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		String fromNum = param.getPersonFromNum();
		String toNum = param.getPersonToNum();
		IMetaDataPK cussQryPK = new MetaDataPK(
				"com.kingdee.eas.basedata.person.app.PersonQuery");

		IQueryExecutor exec = QueryExecutorFactory.getLocalInstance(ctx,
				cussQryPK);

		EntityViewInfo view = new EntityViewInfo();
		FilterInfo filter = new FilterInfo();
		view.setFilter(filter);
		if (!(StringUtils.isEmpty(fromNum))) {
			filter.getFilterItems().add(
					new FilterItemInfo("number", fromNum,
							CompareType.GREATER_EQUALS));
		}

		if (!(StringUtils.isEmpty(toNum))) {
			filter.getFilterItems()
					.add(
							new FilterItemInfo("number", toNum,
									CompareType.LESS_EQUALS));
		}

		if ((!(param.isLinkSum())) && (!(param.isAllCuAdminPerson()))) {
			filter.getFilterItems().add(
					new FilterItemInfo("CU.id", param.getCuId()));
		}

		view.getSelector().add("id");
		view.getSelector().add("name");
		view.getSelector().add("number");
		exec.setObjectView(view);

		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT distinct PERSON.FID ID, PERSON.FName_").append(
				ctx.getLocale()).append(" NAME, PERSON.FNumber NUMBER");
		sql.append(" FROM T_BD_Person PERSON");
		sql.append(" WHERE 1 = 1");
		if (!(StringUtils.isEmpty(fromNum))) {
			sql.append(" and fnumber >= '");
			sql.append(fromNum);
			sql.append("'");
		}
		if (!(StringUtils.isEmpty(toNum))) {
			sql.append(" and fnumber <= '");
			sql.append(toNum);
			sql.append("'");
		}

		if ((!(param.isLinkSum())) && (!(param.isAllCuAdminPerson()))) {
			sql.append(" and fcontrolunitid = '");
			sql.append(param.getCuId());
			sql.append("'");
		}

		param.setPersonTempTable(putIds2Table(ctx, sql.toString(),
				"personRange", param.isIncludeNullPerson()));
	}

	private String putIds2Table(com.kingdee.bos.Context ctx, String sql,
			String tblDesc, boolean fBlankLine) throws BOSException,
			EASBizException {
		String tmpIdsTable = null;

		if (StringUtils.isEmpty(sql))
			return tmpIdsTable;

		String ln = ctx.getLocale().getLanguage();
		String idsTblDefine = "( FId varchar(44), FName_" + ln
				+ " nvarchar(400), FNumber nvarchar(80) )";

		tmpIdsTable = TempTableUtil.createTempTable(ctx, idsTblDefine);

		TempTablePool t = TempTablePool.getInstance(ctx);
		try {
			t.createIndex(tmpIdsTable, "FId", false, false);
		} catch (Exception e) {
			e.printStackTrace(System.out);
		}

		Connection con = null;
		Statement stmt = null;
		try {
			con = getConnection(ctx);
			stmt = con.createStatement();

			StringBuffer inSql = new StringBuffer();
			inSql.append("insert into ").append(tmpIdsTable).append(" ")
					.append(sql);
			logger.debug(tblDesc + " idsTmpTable " + tmpIdsTable + "\r\n"
					+ inSql);
			stmt.execute(inSql.toString());

			if (fBlankLine) {
				inSql.setLength(0);
				inSql.append(" insert into ").append(tmpIdsTable);
				inSql.append(" select null, null, null");
				inSql.append(" union all ");
				inSql.append(" select '").append("z").append("', null, null");
				logger.debug(tblDesc + " null idsTmpTable " + tmpIdsTable
						+ "\r\n" + inSql);
				stmt.execute(inSql.toString());
			}

			try {
				t.analyzeTable(con, tmpIdsTable);
			} catch (Exception e) {
				e.printStackTrace(System.out);
			}
		} catch (SQLException e) {
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			SQLUtils.cleanup(stmt, con);
		}

		return tmpIdsTable;
	}

	private void insertItemRptData(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		String oql = "select id, accountPeriodType.id where id = '"
				+ param.getCompanyId() + "'";

		ICompanyOrgUnit iCompanyOrgUnit = CompanyOrgUnitFactory
				.getLocalInstance(ctx);

		CompanyOrgUnitInfo companyInfo = iCompanyOrgUnit
				.getCompanyOrgUnitInfo(oql);

		insertItemRptStartBalBills(ctx, param, companyInfo);
		removeItemBizBalZeroData(ctx, param);
	}

	private void insertItemRptStartBalBills(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param, CompanyOrgUnitInfo companyInfo)
			throws BOSException, EASBizException {
		SystemEnum sysEnum = (param.isArRpt()) ? SystemEnum.ACCOUNTSRECEIVABLE
				: SystemEnum.ACCOUNTSPAYABLE;

		PeriodInfo startPeriod = SystemStatusCtrolUtils.getStartPeriod(ctx,
				sysEnum, companyInfo);

		if (startPeriod == null) {
			throw new ArApCommonException(
					ArApCommonException.AP_STARTPERIOD_NULL);
		}

		PeriodInfo currPeriod = SystemStatusCtrolUtils.getCurrentPeriod(ctx,
				sysEnum, companyInfo);

		if (param.getBeginDate().before(startPeriod.getBeginDate())) {
			param.setBeginDate(startPeriod.getBeginDate());
		}

		PeriodInfo qryStartPeriod = PeriodUtils.getPeriodInfo(ctx, param
				.getBeginDate(), companyInfo);

		if (qryStartPeriod == null) {
			throw new ArApCommonException(
					ArApCommonException.DATE_NOT_IN_PERIOD);
		}

		Connection con = null;
		Statement stmt = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtFldNull = null;
		ResultSet rs = null;

		String tempTable = param.getTempTable();
		try {
			con = getConnection(ctx);
			stmt = con.createStatement();

			stmt.execute(ItemRptSqlBuilder.blankStartSql(ctx, param,
					qryStartPeriod, true));

			param.setPeriodId(qryStartPeriod.getId().toString());

			TempTablePool t = TempTablePool.getInstance(ctx);
			try {
				t.analyzeTable(con, param.getTempTable());
			} catch (Exception ex) {
				ex.printStackTrace(System.out);
			}

			String temp = ItemRptSqlBuilder.getItemStartSql(ctx, param,
					currPeriod);
			String fields = "FCussAcctId";
			if (param.isSumByDept())
				fields = "FAdminOrgId";
			else if (param.isSumByPerson())
				fields = "FPersonId";

			String tempTable_calcu = TempTableUtil
					.createTempTable(ctx,
							"(fbalance decimal(28, 10) NOT NULL DEFAULT 0, FId varchar(44) NOT NULL)");

			StringBuffer uSql_calcu = new StringBuffer();
			uSql_calcu.append("insert into " + tempTable_calcu);
			uSql_calcu.append("\r\n (fbalance,FId)");
			uSql_calcu.append("\r\n select bal.FBalance,T1.FId ");
			uSql_calcu.append(" from ( ");
			uSql_calcu.append(temp);
			uSql_calcu.append(" ) bal ");
			uSql_calcu.append(" inner join " + tempTable + " as T1 ");
			uSql_calcu.append(" on (T1.FCurrency = bal.FCurrency ");
			uSql_calcu.append(" and ((");

			uSql_calcu.append(" bal.").append(fields).append(" = 'z' ");
			uSql_calcu.append(" and (T1.").append(fields).append(" is null ");
			uSql_calcu.append(" or T1.").append(fields).append(" = '')) ");
			uSql_calcu.append(" or ");
			uSql_calcu.append(" (bal.").append(fields).append(" is not null ");
			uSql_calcu.append(" and bal.").append(fields).append(" = T1.")
					.append(fields);
			uSql_calcu.append(" )");
			uSql_calcu.append(" ))");
			uSql_calcu.append(" where T1.FPeriodId = '").append(
					qryStartPeriod.getId().toString()).append("'");
			uSql_calcu.append(" and bal.FBalance <>0");
			stmt.execute(uSql_calcu.toString());

			StringBuffer uSql = new StringBuffer();
			uSql.append(" update " + tempTable + " as T ");
			uSql.append(" set (FBalance,FId) = (");

			uSql.append("select fbalance,fid from " + tempTable_calcu
					+ " as t1 ");
			uSql.append("\r\n where T.fid = t1.fid");
			uSql.append(" )");
			stmt.execute(uSql.toString());
			TempTableUtil.releasTempTable(ctx, tempTable_calcu);

			insertItemRptBills(ctx, param, companyInfo, qryStartPeriod, stmt);
			try {
				t.analyzeTable(con, param.getTempTable());
			} catch (Exception ex) {
				ex.printStackTrace(System.out);
			}
		} catch (SQLException ex) {
			throw new BOSException(ex);
		} finally {
			SQLUtils.cleanup(pstmt);
			SQLUtils.cleanup(pstmtFldNull);
			SQLUtils.cleanup(rs, stmt, con);
		}
	}

	private void insertItemRptBills(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param, CompanyOrgUnitInfo companyInfo,
			PeriodInfo qryStartPeriod, Statement stmt) throws BOSException,
			EASBizException, SQLException {
		ArrayList sqlList = new ArrayList();
		if (param.isByPeriod()) {
			PeriodCollection periods = ArApReportHelper.getPeriodCollection(
					ctx, param.getBeginDate(), param.getEndDate(), companyInfo);

			if ((periods != null) && (periods.size() > 0)) {
				java.sql.Date qryBeginDate = param.getBeginDate();
				java.sql.Date qryEndDate = param.getEndDate();

				PeriodInfo periodInfo = null;
				String prePeriodId = null;
				int i = 0;
				for (int n = periods.size(); i < n; ++i) {
					periodInfo = periods.get(i);
					if (i > 0) {
						param.setBeginDate(periodInfo.getBeginDate());
					}

					param.setEndDate(new java.sql.Date(periodInfo.getEndDate()
							.getTime() + 86400000L));

					String periodId = periodInfo.getId().toString();
					param.setPeriodId(periodId);

					if (!(periodInfo.getId().equals(qryStartPeriod.getId()))) {
						stmt.execute(ItemRptSqlBuilder.blankStartSql(ctx,
								param, periodInfo, false));

						stmt.executeUpdate(ItemRptSqlBuilder
								.updatePeriodStartBalSql(param, prePeriodId));
					}

					java.util.Locale locale = ctx.getLocale();
					String tempTable = param.getTempTable();

					StringBuffer sql = new StringBuffer();
					sql.append("insert into ").append(tempTable);
					sql
							.append("\r\n (FBillId, FCurrNum, FCurrency, FCussType, FCussNum,");
					sql.append(" FCussName, FCussAcctId,\r\n");
					sql.append(" FAdminOrg, FAdminOrgNum, FAdminOrgId,");
					sql.append(" FPerson, FPersonNum, FPersonId,\r\n");
					sql.append(" FBillDate, FBillType, FRecPayType, FBillNum,");
					sql.append(" FVoucherType, FVoucherNum, FDescription,\r\n");

					sql.append(" FContractNumber,");
					if (param.isShowMaterialItem()) {
						sql
								.append(" FEntrySeq, FEntryId, FMaterialId, FExpenseItemId, FQuantity, ");
					}
					sql
							.append(" FDebitAmt, FCreditAmt, FBalance, FPeriodId,\r\n");
					sql.append(" FPeriodYear, FPeriodNumber, ");
					sql.append("FMainBillType, FDataType, Fid) \r\n");

					if (param.isArRpt()) {
						sqlList.add(sql
								+ new RptArBillSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptRecBillSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptBadAcctSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptBadRecASql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptBadRec_BadSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptArVRec_ArVApSql().getItemQuerySql(
										param, locale));

						sqlList.add(sql
								+ new RptArVRecBadVRec_RecSql()
										.getItemQuerySql(param, locale));

						sqlList.add(sql
								+ new RptArPreRecVPrePay_RecVPaySql()
										.getItemQuerySql(param, locale));

						sqlList.add(sql
								+ new RptAr_ApVArSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptArPrePayVPreRec_PayVRecSql()
										.getItemQuerySql(param, locale));

						sqlList.add(sql
								+ new RptArTransSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptRecTransSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptAr2PrepayTransSql().getItemQuerySql(
										param, locale));
					} else {
						sqlList.add(sql
								+ new RptApBillSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptPayBillSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptApVPay_ApVArSql().getItemQuerySql(
										param, locale));

						sqlList.add(sql
								+ new RptApApVPay_PaySql().getItemQuerySql(
										param, locale));

						sqlList.add(sql
								+ new RptApPrePayVPreRec_PayVRecSql()
										.getItemQuerySql(param, locale));

						sqlList.add(sql
								+ new RptApArVAp_ApSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptApPreRecVPrePay_RecVPaySql()
										.getItemQuerySql(param, locale));

						sqlList.add(sql
								+ new RptApTransSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptPayTransSql().getItemQuerySql(param,
										locale));

						sqlList.add(sql
								+ new RptAp2PrerecTransSql().getItemQuerySql(
										param, locale));
					}

					prePeriodId = periodId;
				}

				param.setBeginDate(qryBeginDate);
				param.setEndDate(qryEndDate);
			}

		} else {
			param.setEndDate(new java.sql.Date(
					param.getEndDate().getTime() + 86400000L));

			java.util.Locale locale = ctx.getLocale();
			String tempTable = param.getTempTable();

			StringBuffer sql = new StringBuffer();
			sql.append("insert into ").append(tempTable);
			sql
					.append("\r\n (FBillId, FCurrNum, FCurrency, FCussType, FCussNum,");
			sql.append(" FCussName, FCussAcctId,\r\n");
			sql.append(" FAdminOrg, FAdminOrgNum, FAdminOrgId,");
			sql.append(" FPerson, FPersonNum, FPersonId,\r\n");
			sql.append(" FBillDate, FBillType, FRecPayType, FBillNum,");
			sql.append(" FVoucherType, FVoucherNum, FDescription,\r\n");

			sql.append(" FContractNumber,");
			if (param.isShowMaterialItem()) {
				sql
						.append(" FEntrySeq, FEntryId, FMaterialId, FExpenseItemId, FQuantity, ");
			}
			sql.append(" FDebitAmt, FCreditAmt, FBalance, FPeriodId,\r\n");
			sql.append(" FPeriodYear, FPeriodNumber, ");
			sql.append("FMainBillType, FDataType,Fid) \r\n");

			if (param.isArRpt()) {
				sqlList.add(sql
						+ new RptRecBillSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptArBillSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptBadAcctSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptBadRecASql().getItemQuerySql(param, locale));

				sqlList
						.add(sql
								+ new RptBadRec_BadSql().getItemQuerySql(param,
										locale));

				sqlList.add(sql
						+ new RptArVRec_ArVApSql().getItemQuerySql(param,
								locale));

				sqlList.add(sql
						+ new RptArVRecBadVRec_RecSql().getItemQuerySql(param,
								locale));

				sqlList.add(sql
						+ new RptArPreRecVPrePay_RecVPaySql().getItemQuerySql(
								param, locale));

				sqlList.add(sql
						+ new RptAr_ApVArSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptArPrePayVPreRec_PayVRecSql().getItemQuerySql(
								param, locale));

				sqlList.add(sql
						+ new RptArTransSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptRecTransSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptAr2PrepayTransSql().getItemQuerySql(param,
								locale));
			} else {
				sqlList.add(sql
						+ new RptApBillSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptPayBillSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptApVPay_ApVArSql().getItemQuerySql(param,
								locale));

				sqlList.add(sql
						+ new RptApApVPay_PaySql().getItemQuerySql(param,
								locale));

				sqlList.add(sql
						+ new RptApPrePayVPreRec_PayVRecSql().getItemQuerySql(
								param, locale));

				sqlList
						.add(sql
								+ new RptApArVAp_ApSql().getItemQuerySql(param,
										locale));

				sqlList.add(sql
						+ new RptApPreRecVPrePay_RecVPaySql().getItemQuerySql(
								param, locale));

				sqlList.add(sql
						+ new RptApTransSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptPayTransSql().getItemQuerySql(param, locale));

				sqlList.add(sql
						+ new RptAp2PrerecTransSql().getItemQuerySql(param,
								locale));
			}

		}

		Connection con = null;
		try {
			TempTablePool t;
			con = getConnection(ctx);

			DBUtil.execBatchSql(con, sqlList, true);

			if (param.isShowMaterialItem()) {
				t = TempTablePool.getInstance(ctx);
				try {
					t.analyzeTable(con, param.getTempTable());
				} catch (Exception ex) {
					ex.printStackTrace(System.out);
				}

				ArrayList sqlList2 = new ArrayList();
				String tbl = param.getTempTable();
				sqlList2.add(ItemRptSqlBuilder.updateVerifBillEntryMaterialSql(
						ctx, tbl, true));

				sqlList2.add(ItemRptSqlBuilder.updateVerifBillEntryExpenseSql(
						ctx, tbl, true));

				sqlList2.add(ItemRptSqlBuilder.updateItemRptBillRowTypeSql(ctx,
						tbl, true));

				sqlList2.add(ItemRptSqlBuilder.updateVerifBillEntryMaterialSql(
						ctx, tbl, false));

				sqlList2.add(ItemRptSqlBuilder.updateVerifBillEntryExpenseSql(
						ctx, tbl, false));

				sqlList2.add(ItemRptSqlBuilder.updateItemRptBillRowTypeSql(ctx,
						tbl, false));

				DBUtil.execBatchSql(con, sqlList2, true);
			}

			stmt.execute(ItemRptSqlBuilder.getItemPeriodSumSql(ctx, param));

			if (param.isAllCurrency()) {
				t = TempTablePool.getInstance(ctx);
				try {
					t.analyzeTable(con, param.getTempTable());
				} catch (Exception e) {
					e.printStackTrace(System.out);
				}

				stmt.execute(ItemRptSqlBuilder.getItemAllCurrencySumSql(param));
			}
		} finally {
			SQLUtils.cleanup(con);
		}
	}

	private void removeItemBizBalZeroData(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		if ((!(param.isBalZero())) && (!(param.isBizZero())))
			return;

		String tempTable = param.getTempTable();
		String field = "";
		if (param.isSumByCussAcct())
			field = "FCussAcctId";
		else if (param.isSumByDept())
			field = "FAdminOrgId";
		else if (param.isSumByPerson())
			field = "FPersonId";

		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmtFldNull = null;
		try {
			con = getConnection(ctx);
			stmt = con.createStatement();

			String dSql = "delete " + tempTable
					+ " where FCurrency = ? and FPeriodId  = ? and " + field
					+ " = ? ";

			String dFldNullSql = "delete " + tempTable
					+ " where FCurrency = ? and FPeriodId  = ? and " + field
					+ " is null and FDataType <= " + 6;

			pstmt = con.prepareStatement(dSql);
			pstmtFldNull = con.prepareStatement(dFldNullSql);

			StringBuffer sql = new StringBuffer();
			if (param.isBizZero()) {
				sql.append("select t1.FCurrency, t1.FPeriodId, t1.");
				sql.append(field).append(" from ");
				sql.append(tempTable).append(" t1 \r\n where ");
				sql.append(" t1.FDebitAmt = 0 and t1.FCreditAmt = 0");
				sql.append(" and t1.FDataType = ");
				sql.append(5);

				logger.debug("delBizSql :\r\n" + sql);
				logger.debug("delBizBalSql: \r\n" + dSql);
				logger.debug("delBizFldNullSql :\r\n" + dFldNullSql);

				rs = stmt.executeQuery(sql.toString());
				removeItemAllCurrBizBal(pstmt, pstmtFldNull, rs);
			}

			if (param.isBalZero()) {
				sql.delete(0, sql.length());
				sql.append("select t1.FCurrency, t1.FPeriodId, t1.");
				sql.append(field).append(" from ");
				sql.append(tempTable);
				sql.append(" t1 \r\n left join ");
				sql.append(tempTable);
				sql.append(" t2 \r\n on ");
				sql.append(" t1.FPeriodId = t2.FPeriodId \r\n ");
				sql.append("where (t1.FBalance + ");
				sql.append(" t2.FDebitAmt -  t2.FCreditAmt ) = 0  ");
				sql.append("\r\n and (t1.FDataType = ");
				sql.append(0);
				sql.append(" or t1.FDataType = ");
				sql.append(1);
				sql.append(") and t2.FDataType = ");
				sql.append(5);
				sql.append(" and t1.FCurrency = t2.FCurrency ");
				sql.append("\r\n and (t1.").append(field).append(" = ");
				sql.append(" t2.").append(field).append(" or (t1.");
				sql.append(field).append(" is null and t2.");
				sql.append(field).append(" is null))");

				logger.debug("delBalSql  :\r\n" + sql);
				logger.debug("delBizBalSql: \r\n" + dSql);
				logger.debug("delBizFldNullSql :\r\n" + dFldNullSql);

				rs = stmt.executeQuery(sql.toString());
				removeItemAllCurrBizBal(pstmt, pstmtFldNull, rs);
			}

			if (param.isAllCurrency()) {
				if (param.isBizZero()) {
					stmt.execute(ItemRptSqlBuilder
							.delAllCurrencyBizZeroSql(tempTable));
				}

				if (param.isBalZero()) {
					sql.delete(0, sql.length());
					sql.append("delete ").append(tempTable);
					sql.append(" where FDataType = ");
					sql.append(7);
					sql.append("\r\n and FCurrency in (select FCurrency from ");
					sql.append("\r\n (select FCurrency, count(1) FCount from ");
					sql.append(tempTable).append(" group by FCurrency ) t2 ");
					sql.append("\r\n where t2.FCount = 1)");
					logger.debug("itemRpt AllCurrency delBalZero Sql: \r\n"
							+ sql);

					stmt.execute(sql.toString());
				}
			}
		} catch (SQLException ex) {
			throw new BOSException(ex);
		} finally {
			SQLUtils.cleanup(rs, pstmt, con);
			SQLUtils.cleanup(stmt);
			SQLUtils.cleanup(pstmtFldNull);
		}
	}

	private void removeItemAllCurrBizBal(PreparedStatement pstmt,
			PreparedStatement pstmtFldNull, ResultSet rs) throws SQLException {
		boolean fUpdate = false;
		boolean fFldNullDel = false;

		while (rs.next()) {
			if (rs.getString(3) == null) {
				pstmtFldNull.setString(1, rs.getString(1));
				pstmtFldNull.setString(2, rs.getString(2));

				pstmtFldNull.addBatch();
				fFldNullDel = true;
			} else {
				pstmt.setString(1, rs.getString(1));
				pstmt.setString(2, rs.getString(2));
				pstmt.setString(3, rs.getString(3));

				pstmt.addBatch();
			}

			fUpdate = true;
		}

		if (fUpdate) {
			pstmt.executeBatch();
			if (fFldNullDel) {
				pstmtFldNull.executeBatch();
			}
		}
	}

	private IReportData getArApItemData(com.kingdee.bos.Context ctx,
			ItemRptQueryParam param) throws BOSException, EASBizException {
		String sql = ItemRptSqlBuilder.getRptDataSql(param);
		if (StringUtils.isEmpty(sql))
			return null;

		IReportData rptData = null;

		Connection cn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String balanceCol = "FBalance";
		try {
			cn = getConnection(ctx);
			pstmt = cn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			int start = param.getStartIndex();
			int end = param.getEndIndex();
			int iType = 0;
			int iMainType = 0;

			String billType = "";
			String col = "";
			List rptFlds = param.getReportFields();
			List dataLst = new ArrayList();
			ReportInfo rptInfo = null;
			BigDecimal balance = SysConstant.BIGZERO;

			String currency = null;
			String asstActId = null;
			String balTag = null;
			String preBalTag = null;
			java.util.Map balMap = new HashMap();

			int dataType = -1;
			int row = 0;
			while (rs.next()) {
				++row;

				dataType = rs.getInt("FDataType");
				currency = rs.getString("FCurrNum");
				asstActId = rs.getString("FCussAcctId");
				balTag = asstActId + "-" + currency;
				if (dataType == 0) {
					balance = rs.getBigDecimal(balanceCol);
					balance = (balance == null) ? SysConstant.BIGZERO : balance;
				} else if (dataType == 1) {
					if ((preBalTag != null) && (!(balTag.equals(preBalTag)))) {
						balance = (BigDecimal) balMap.get(balTag);
						balance = (balance == null) ? SysConstant.BIGZERO
								: balance;
					}

				} else if (dataType == 2) {
					BigDecimal amt = rs.getBigDecimal("FDebitAmt");
					if (amt != null) {
						balance = balance.add(amt);
					}
					amt = rs.getBigDecimal("FCreditAmt");
					if (amt != null) {
						balance = balance.subtract(amt);
					}
				}

				balMap.put(balTag, balance);

				if (ArApReportHelper.isInRange(row, start, end)) {
					rptInfo = new ReportInfo();
					int i = 0;
					for (int n = rptFlds.size(); i < n; ++i) {
						col = rptFlds.get(i).toString();

						if (col.equalsIgnoreCase("FBillType")) {
							if (dataType != 2)
								break;

							iType = rs.getInt(col);
							iMainType = rs.getInt("FMainBillType");
							if ((iMainType == 1) || (iMainType == 2)) {
								billType = rs.getString("FRecPayType");
							} else {
								billType = getBillType(iType, iMainType, ctx
										.getLocale());
							}
							rptInfo.put(col, billType);
						} else {
							rptInfo.put(col, rs.getObject(col));
						}
					}
					if (dataType > 2) {
						rptInfo.setIsSumLine(dataType == 5);
						rptInfo.setIsTotalLine(dataType == 7);
						rptInfo.put(balanceCol, null);
					} else {
						rptInfo.put(balanceCol, balance);
					}
					dataLst.add(rptInfo);
				} else if (row > end) {
					break;
				}
				preBalTag = asstActId + "-" + currency;
			}

			rptData = new ArApReportData(dataLst);
			rptData.setTempTable(param.getTempTable());
		} catch (SQLException e) {
			throw new BOSException(e);
		} finally {
			SQLUtils.cleanup(rs, pstmt, cn);
		}

		return rptData;
	}

	private String itemRptTempTableDefine() {
		StringBuffer sql = new StringBuffer();
		sql.append(" (FBillId varchar(44), ");
		sql.append(" FCurrNum varchar(80), ");
		sql.append(" FCurrency nvarchar(200), ");
		sql.append(" FCussType nvarchar(100), ");
		sql.append(" FCussNum nvarchar(100), ");
		sql.append(" FCussName nvarchar(400), ");
		sql.append(" FCussAcctId varchar(44), ");
		sql.append(" FAdminOrg nvarchar(200), ");
		sql.append(" FAdminOrgId varchar(44), ");
		sql.append(" FAdminOrgNum nvarchar(100), ");
		sql.append(" FPerson nvarchar(200), ");
		sql.append(" FPersonId varchar(44), ");
		sql.append(" FPersonNum nvarchar(100), ");
		sql.append(" FBillDate dateTime, ");
		sql.append(" FBillType int DEFAULT -1, ");
		sql.append(" FRecPayType nvarchar(100), ");
		sql.append(" FBillNum nvarchar(100), ");
		sql.append(" FVoucherType nvarchar(100), ");
		sql.append(" FVoucherNum nvarchar(100), ");
		sql.append(" FDescription nvarchar(200), ");
		sql.append(" FEntrySeq int, ");
		sql.append(" FEntryId varchar(44), ");
		sql.append(" FMaterialId varchar(44), ");
		sql.append(" FExpenseItemID varchar(44), ");
		sql.append(" FMaterialName nvarchar(100), ");
		sql.append(" FSpecName nvarchar(100), ");
		sql.append(" FMeasureUnitName nvarchar(100), ");
		sql.append(" FQtyPrecision int DEFAULT 2, ");
		sql.append(" FQuantity decimal(28, 16) , ");
		sql.append(" FDebitAmt decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FCreditAmt decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FBalance decimal(28, 10) NOT NULL DEFAULT 0, ");
		sql.append(" FPeriodId varchar(44), ");
		sql.append(" FPeriodYear int, ");
		sql.append(" FPeriodNumber int, ");

		sql.append(" FMainBillType int NOT NULL DEFAULT -1, ");

		sql.append(" FCurrSumOrder int NOT NULL DEFAULT 0, ");

		sql.append(" FDataType int NOT NULL DEFAULT -1,");

		sql.append(" FContractNumber nvarchar(80) ,");

		sql.append(" FId varchar(44) NOT NULL");
		sql.append(")");

		return sql.toString();
	}

	protected void _releaseTempTable(com.kingdee.bos.Context ctx,
			String tempTable) throws BOSException {
		try {
			TempTableUtil.releasTempTable(ctx, tempTable);
		} catch (Exception e) {
			logger.error("cannot release tmpTable: " + tempTable, e);
		}
	}
}